Contents
Related Content
Create a one-to-many relationship in Access Learn how to create a one-to-many relationship in Access 2010.

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. 

schematic one to many relationship

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.

  1. How many entities of B can belong to entity A?
  2. 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.

Examples

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.