1:N Relationship

The 1:N relationship is the most common type of relationship between records. When Codd developed the relational database model, he decided that 1:N relationships would be represented by storing a pointer in each child record to their unique parent record. Children point to their parents in the relational model; parents don't point to their children. This is a fundamental difference between the relational model and the earlier types of databases.

There is a 1:N relationship between employees and paychecks. Every employee can have lots of paychecks (including zero), but every paycheck must belong to a single employee. If SSN is the primary key of the employee table, then the relationship is represented by storing a copy of the SSN in the paycheck table. This copy of the SSN is called a foreign key. It is a pointer in the paycheck table to another (i.e., foreign) record in the employee table. Paychecks point to their employee.

There is a 1:N relationship between wholesale suppliers and products. Every supplier can provide multiple products, but every product must be provided by a single supplier. If SupplierID is the primary key of the supplier table, then the relationship is represented by storing a copy of SupplierID in the product table. It is a foreign key pointer from the product table (child) to the supplier table (parent). Products point to their supplier.

There is a 1:N relationship between customers and purchases. Every customer can make lots of purchases, but every purchase must be made by a single customer. If CustomerID is the primary key of the customer table, then the relationship is represented by storing a copy of the CustomerID in the purchase table. It is a foreign key pointer from the purchase table (child) to the customer table (parent). Purchases point to their customer.

In all cases, children point to their parents, not the other way around.

More FAQs