Primary Keys and Unique Identifiers
The relationship has two jobs: it shows how entity instances are connected, and it tells you where the identifying keys belong.
A Single Parent, Many Related Records
Imagine one customer connected to several orders. The customer is one entity instance, while the orders are multiple instances of another entity. This is the central pattern of a one-to-many relationship: one instance on one side is associated with multiple instances on the other side.
The relationship has two jobs: it shows how entity instances are connected, and it tells you where the identifying keys belong.
Tracing the Relationship
Read the relationship from the instance level. One particular Customer can be associated with several Order instances. Each Order instance in this example connects back to one Customer instance. The important distinction is that the relationship is not saying that there is only one order overall. It is saying that one entity instance can connect to multiple instances of the other entity.
What do you think happens?
In a one-to-many relationship, which side should be able to connect to several related records?
Reveal answer
The one side can connect to multiple instances on the many side.
A one-to-many relationship connects one entity instance to multiple instances of another entity.
Reading Crow's Foot Symbols
In Crow's Foot notation, a simple vertical line represents the one end of a relationship. A crow's foot symbol, which looks like three lines spreading outward, represents the many end.
The symbols provide a visual way to identify cardinality, meaning how many instances participate at each end. Find the simple vertical line to locate the one side. Find the three spreading lines of the crow's foot to locate the many side. Once those ends are identified, key placement follows the same pattern.
Placing the Keys
The primary key is placed on the one side. It uniquely identifies each instance of that entity. The foreign key is placed on the many side. It stores the primary key value from the one side, creating the link back to the related one-side instance.
Connecting Orders to a Customer
A Customer entity has primary key customer_id. An Order entity is on the many side of the relationship. Where should the identifying values be placed?
- 1Identify the one side
The Customer entity is the one side in this generated example because one customer instance is associated with multiple order instances.
- 2Place the primary key
Place
customer_idas the primary key on Customer. The primary key uniquely identifies each Customer instance. - 3Place the foreign key
Place
customer_idas a foreign key on Order. Each Order record stores the value that refers back to the primary key on Customer. - 4Follow the link
The repeated foreign-key values on many Order records can point back to the appropriate Customer primary-key value, connecting those orders to one customer instance.
The primary key belongs on the one-side Customer entity, and the foreign key belongs on the many-side Order entity.
The placement rule is concise: primary key on the one side; foreign key on the many side, referencing the primary key.
Checking the Link
After placing the keys, trace a value from a many-side record back to the one-side entity. If an Order stores a customer_id value, that value should refer to the customer_id of an existing Customer. This is the role of referential integrity: every foreign key value actually refers to an existing primary key value.
Common mistake Putting the foreign key on the one side.
What it looks likePlacing the linking customer_id only on Customer when Customer is the one side.
Why it failsThe source-side rule for a one-to-many relationship places the foreign key on the many side, where multiple related records can store the value that points back to one primary key.
Do this insteadPlace the primary key on the one side and the foreign key on the many side.
Common mistake Confusing the crow's foot with the one end.
What it looks likeTreating the three spreading lines as a symbol for one.
Why it failsCrow's Foot notation uses the simple vertical line for one and the crow's foot for many.
Do this insteadLook for the vertical line to find one and the spreading crow's foot to find many.
Common mistake Treating a primary key as a relationship symbol.
What it looks likeUsing the primary key to decide which end has many records.
Why it failsThe symbols identify cardinality, while the key placement follows that cardinality.
Do this insteadFirst identify the one and many ends from the notation, then place the primary and foreign keys.
Practice the Placement Rule
A Library entity is connected to multiple Book entities in a one-to-many relationship. Identify the one side, the many side, the location of the primary key, and the location of the foreign key. Then explain what the foreign-key value refers to.
Hints
- Use the relationship cardinality before deciding where keys go.
- The primary key belongs on the one side.
- The foreign key belongs on the many side and references the primary key.
- 1To solve the exercise, treat Library as the one side and Book as the many side in the generated scenario. The primary key belongs on Library. The foreign key belongs on Book and stores the primary-key value that connects each Book instance to one Library instance.
Key Takeaways
- A one-to-many relationship connects one instance of one entity to multiple instances of another entity.
- Crow's Foot notation uses a vertical line for one and a three-line crow's foot for many.
- The primary key is placed on the one side and uniquely identifies each instance there.
- The foreign key is placed on the many side and references the primary key on the one side.
- This key structure supports reliable links and referential integrity.