Primary Keys and Unique Identifiers
A one-to-many relationship connects one entity instance to multiple instances of another entity.
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
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?
Identify the one side: The Customer entity is the one side in this generated example because one customer instance is associated with multiple order instances.
Place the primary key: Place customer_id as the primary key on Customer. The primary key uniquely identifies each Customer instance.
Place the foreign key: Place customer_id as a foreign key on Order. Each Order record stores the value that refers back to the primary key on Customer.
Follow 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.
Putting the foreign key on the one side.
The 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.
Fix:
Place the primary key on the one side and the foreign key on the many side.Confusing the crow's foot with the one end.
Crow's Foot notation uses the simple vertical line for one and the crow's foot for many.
Fix:
Look for the vertical line to find one and the spreading crow's foot to find many.Treating a primary key as a relationship symbol.
The symbols identify cardinality, while the key placement follows that cardinality.
Fix:
First 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.
- To 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.