Database design tutorial

The one-to-many relationship

When one data item can be linked to 0, 1 or many other data items, you are dealing with a one-to-many relationship. You already saw one example of a one-to-many relationship. One customer can be linked to multiple contact moments. And vice versa each contact moment is linked to only 1 customer. The same applies to the relationship that exists between a woman and her children. A woman can have 0, 1 or many children and each child has one woman as its mother.

In the database we are designing for the customer contact system the Customers table plays the role of table A. The ContactMoments table corresponds to table B in the image. Note how every customer row in table A can have 0, 1 or multiple contact moment rows in table B.

The customer_id field is the primary key in table A. It uniquely identifies a customer row in that table. The customer ID is present in table B as well. In table B the customer_id is called a foreign key. A foreign key field is a field that points to the primary key field of another table. In the relational model data are linked using the foreign key. In our database design example, each contact moment is linked to a customer via the customer_id foreign key field.

enlarge

In the ContactMoment table, customer_id is a foreign key. It points to the customer_id field in the Customers table. The contact_moment_id is the primary key (pk) in the Customers table.

1. 1  
2. 2  
3. 3  
4. 4  
5. 5  
6. 6  
7. 7 
8. 8  
9. 9  
10. 10  
11. 11  
12. 12  
13. 13  
14. 14  
15. 15  
16. 16