The one-to-many relationship
On the page about linking tables I showed you how data from different tables can be linked by defining a foreign key relationship. You saw how orders are linked to customers by including the customer_id as a foreign key in the orders table.
Another example of a one-to-many relationship is the relationship that exists between a mother and her children. A mother can have many children and each child has only one mother.
(Technically it would be better to speak of a woman and her children instead of a mother and her children, because in a one-to-many relationship a mother can have 0, 1 or many children and a mother with 0 children isn't technically a mother. But let's just play along, ok?)
When one record in table A can be linked to 0, 1 or many records in table B, you are dealing with a one-to-many relationship. In the relational model a one-to-many relationship is modelled using two tables.
A schematic representation of a one-to-manyrelationship. A record in table A has 0,1 or many associatedrecords in table B.
How to identify a one-to-many relationship?
When you have two entities ask yourself these questions.
- How many entities of B can belong to entity A?
- How many entities of A can belong to entity B?
Of the answer to question 1 is many and the answer to question 2 is one (or possibly none) you are dealing with a one-to-many relationship.
Some examples of one-to-many relationships are listed below.
- A car and its parts. Each part belongs to one car and one car has multiple parts.
- A movie theater and screens. One theatre usually has multiple screens and each screen belongs to one theatre.
- An ERD and its tables. An entity-relationship diagram has one or more tables and each of thos tables belongs to one diagram.
- Houses in a street. One street had multiple houses and a house belongs to one street.