Entity-Relationship Diagrams and Data Modeling
A one-to-many relationship connects one entity instance to multiple instances of another entity.
From One Record to Many
A one-to-many relationship describes a connection in which one instance of an entity is associated with multiple instances of another entity. The relationship has two different sides: the one side and the many side. Learning to identify those sides is the first step toward placing keys correctly in a data model.
The central question is: can one instance on this side connect to multiple instances on the other side? If so, the side with the single instance is the one side, and the side with multiple associated instances is the many side.
Following the Relationship
Imagine two entities named Customer and Order. In this generated example, one Customer instance can be connected to several Order instances. The Customer instance is therefore on the one side, while the related Order instances are on the many side. The relationship does not mean that every customer must have several orders; it identifies the direction of the connection: one instance of the first entity can be associated with multiple instances of the second entity.
Identifying the two sides
A generated data model connects Customer and Order. One Customer instance is associated with Order 1, Order 2, and Order 3. Which entity is on the one side, and which is on the many side?
Count the Customer instances in the relationship: The example focuses on one Customer instance.
Count the associated Order instances: That Customer instance is associated with multiple Order instances.
Assign the sides: Customer is the one side because one Customer instance is involved. Order is the many side because multiple Order instances are involved.
Customer is on the one side, and Order is on the many side.
Reading Crow's Foot Symbols
Crow's Foot notation visually represents cardinality, meaning how many instances participate at each end of a relationship. The one end uses a simple vertical line. The many end uses a crow's foot symbol made of three lines spreading outward from the relationship line. The spreading shape resembles a bird's foot and visually suggests many connections branching out.
| Symbol | Meaning | Side identified |
|---|---|---|
| Simple vertical line | Exactly one instance is involved at that end | One side |
| Crow's foot with three spreading lines | Multiple instances are involved at that end | Many side |
Read the symbols before deciding where the keys go. First locate the simple vertical line and the crow's foot. Then label the corresponding entities as one and many. This prevents the common error of placing a foreign key on the wrong side.
Placing Primary and Foreign Keys
In a one-to-many relationship, the primary key is placed on the one side. A primary key 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 back to a customer
Use the generated Customer and Order relationship to determine where each key belongs.
Place the primary key: Customer is on the one side, so its primary key belongs in the Customer entity and uniquely identifies each Customer instance.
Place the foreign key: Order is on the many side, so its foreign key belongs in the Order entity.
Establish the reference: Each Order record stores the primary key value of the Customer instance to which it is related.
The primary key is on Customer, and the foreign key is on Order. The foreign key connects each many-side Order record back to a Customer record on the one side.
Checking a Data Model
A reliable check follows the relationship in order: identify the one and many ends, locate the primary key on the one side, and locate the foreign key on the many side. The following mistakes reverse one of those steps.
Treating the crow's foot as the one side.
The crow's foot represents many. The simple vertical line represents one.
Fix:
Use the vertical line to identify the one end and the crow's foot to identify the many end.Putting the foreign key on the one side.
The foreign key belongs on the many side and references the primary key on the one side.
Fix:
Place the primary key in Customer, the one-side entity, and the foreign key in Order, the many-side entity.Assuming the foreign key identifies the many-side entity by itself.
The source describes the primary key as the value that uniquely identifies each instance of its entity, while the foreign key establishes the link back to the one side.
Fix:
Keep the roles distinct: the primary key uniquely identifies an entity instance, and the foreign key stores a referenced primary key value.
Practice the Key Placement
A generated diagram shows Department on the one side and Employee on the many side. Identify which entity receives the primary key and which entity receives the foreign key. Then explain what the foreign key connects.
Hints
- Start with the relationship sides, not the key names.
- The primary key belongs on the one side.
- The foreign key belongs on the many side and references the primary key on the one side.
What do you think happens?
For the generated Department and Employee relationship, where should the keys be placed?
Reveal answer
Answer: Primary key in Department; foreign key in Employee
Department is on the one side, so its primary key uniquely identifies each Department instance. Employee is on the many side, so its foreign key stores the primary key value that links each Employee record back to a Department record.
Essential Rules
- A one-to-many relationship connects one instance of one entity to multiple instances of another entity.
- In Crow's Foot notation, a simple vertical line identifies the one end.
- A crow's foot with three spreading lines identifies the many end.
- The primary key belongs on the one side and uniquely identifies each instance there.
- The foreign key belongs on the many side and references the primary key on the one side.
Key Takeaways
- One-to-many relationships connect one entity instance with multiple instances of another entity.
- Crow's Foot notation uses a vertical line for one and a crow's foot for many.
- The primary key is placed on the one side of the relationship.
- The foreign key is placed on the many side and references the primary key on the one side.
- Correct key placement helps maintain referential integrity and reliable connections between related data.