Many-to-Many Relationships and Junction Tables
A one-to-many relationship connects one entity instance to multiple instances of another entity.
Start with the Relationship
A many-to-many relationship exists when instances on both sides can be connected to multiple instances on the other side. The key to designing this structure is to work with one-to-many relationships: each original entity connects to multiple records in a junction table. Once you can identify the one side, the many side, and the key placement, the design becomes much easier to read.
Reading One-to-Many Relationships
A one-to-many relationship connects one entity instance to multiple instances of another entity. For example, one record on the one side can be associated with several records on the many side. This relationship gives the database a clear direction for key placement: the primary key belongs to the one-side entity, while the foreign key belongs to the many-side entity and references that primary key.
The one side identifies the entity whose primary key is being referenced. The many side contains the foreign key values that point back to that primary key.
Crow's Foot Symbols
Crow's Foot notation shows relationship cardinality visually. A simple vertical line represents the one end. A crow's foot, drawn as three lines spreading outward from the relationship line, represents the many end. The symbol names help you read the relationship before looking at the tables' columns.
| Symbol | Meaning | Key implication |
|---|---|---|
| Vertical line | One instance of the entity | This entity provides the referenced primary key |
| Crow's foot | Multiple instances of the entity | This entity contains the foreign key referencing the one side |
Reading cardinality and key placement together
Placing the Keys
In a one-to-many relationship, the primary key is placed on the one-side entity. A primary key uniquely identifies each instance of that entity. The foreign key is placed on the many-side entity. It stores a primary key value from the one side, establishing the link back to that entity.
Following the key from one side to many side
Suppose Customer is the one-side entity and Order is the many-side entity. Determine where customer_id belongs in the relationship.
Identify the one side: Customer is on the one side, so its primary key uniquely identifies each Customer instance.
Identify the many side: Order is on the many side, so multiple Order instances can be associated with the one-side entity.
Place the foreign key: The Order entity contains customer_id as a foreign key that references the Customer primary key.
The primary key is on Customer, and the referencing foreign key is on Order.
Building the Junction Table
A junction table is the intermediate entity used to express a many-to-many relationship as two one-to-many relationships. The first original entity is on the one side of a relationship to the junction table, and the second original entity is also on the one side of another relationship to the junction table. The junction table is therefore on the many side in both relationships.
Using the key-placement rule, the junction table receives a foreign key for each one-to-many relationship. The foreign key connected to Student references the Student primary key, and the foreign key connected to Course references the Course primary key. Each junction-table record represents a connection between one Student instance and one Course instance.
| Relationship | One side | Many side | Foreign key location |
|---|---|---|---|
| Student to Enrollment | Student | Enrollment | Enrollment references Student's primary key |
| Course to Enrollment | Course | Enrollment | Enrollment references Course's primary key |
The junction table is the many side in two separate one-to-many relationships
Trace the Key Flow
What do you think happens?
Student and Course are connected through Enrollment. Which entity should contain the foreign keys that reference Student and Course?
Reveal answer
Answer: Enrollment
Enrollment is on the many side of the Student-to-Enrollment relationship and the Course-to-Enrollment relationship. The foreign keys belong on the many side and reference the primary keys on the one sides.
Tracing two one-to-many relationships
Use Student, Course, and Enrollment to determine the role of each entity in the design.
Trace Student to Enrollment: Student is the one side, and Enrollment is the many side. Therefore, Enrollment contains a foreign key referencing Student's primary key.
Trace Course to Enrollment: Course is the one side, and Enrollment is the many side. Therefore, Enrollment contains a foreign key referencing Course's primary key.
Interpret the junction record: A record in Enrollment connects one Student instance with one Course instance. Multiple Enrollment records can connect different Student and Course instances.
The many-to-many design is represented as two one-to-many relationships, with the junction table holding the foreign keys for both relationships.
Mistakes with Cardinality
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:
Identify the crow's-foot end first, then place the foreign key in that entity.Reading the crow's foot as the one end
Crow's Foot notation uses a vertical line for one and a crow's foot for many.
Fix:
Read the simple vertical line as one and the spreading crow's-foot symbol as many.Treating the junction table as one side
In the two one-to-many relationships, Enrollment is on the many side of each relationship.
Fix:
Place the foreign keys in Enrollment so they reference the primary keys of Student and Course.Ignoring referential integrity
Referential integrity requires every foreign key value to refer to an existing primary key value.
Fix:
Check that each foreign key points to an existing primary key on the corresponding one-side entity.
Practice the Design
A Library entity and a Book entity are connected through a junction table named Borrowing. Assume Library and Book are the one-side entities in their respective relationships with Borrowing. Identify the many-side entity, name the two foreign-key references that belong there, and describe which Crow's Foot symbol appears at the Borrowing end.
Hints
- Apply the one-to-many rule separately to Library-to-Borrowing and Book-to-Borrowing.
- The foreign key belongs on the many side and references the primary key on the one side.
- The crow's-foot symbol identifies the many end.
- A many-to-many relationship can be understood through two one-to-many relationships involving a junction table. In each relationship, identify the one side with the vertical line and the many side with the crow's foot. Place the primary key on the one-side entity and the foreign key on the many-side entity. In the junction design, the junction table is the many side in both relationships, so it contains foreign keys that reference the primary keys of the two original entities.
Key Takeaways
- A one-to-many relationship connects one entity instance to 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 on the one side, while the foreign key is on the many side and references that primary key.
- A junction table represents a many-to-many relationship as two one-to-many relationships.
- Correct key placement supports referential integrity and reliable links between related data.