Many-to-Many Relationships and Junction Tables
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.
Try it: From an ER Diagram to Tables
How the cardinality on an entity-relationship diagram decides the schema: each entity becomes a table with a primary key, a one-to-many relationship puts the foreign key on the many side, and a many-to-many relationship needs a junction table — and how sample rows that do not fit a too-narrow cardinality are rejected.
How it works
- Ask the cardinality question from both sides: can one row here link to many rows there, and the other way round? Many on both sides is many-to-many.
- Each entity becomes a table with an INTEGER PRIMARY KEY id.
- One-to-many: the many side gets a foreign-key column referencing the one side's primary key. One-to-one: that foreign key is also UNIQUE.
- Many-to-many: one foreign-key column holds one value per row, so a junction table with a foreign key to each side (and a composite primary key) stores every pairing — two one-to-many relationships.
- Load the sample rows: a second link for the same row would need a second row with the same id, so SQLite rejects it (UNIQUE constraint failed); junction rows always fit.
Default run (17 steps): Model: Customer, Order, Product with Customer places Order (1:N) and Order contains Product (M:N). … Every active link is stored: the schema produced by the mapping rules can hold these rows.
Simplified: A toy relational engine in the browser, not SQLite itself: statements are built from the controls (no SQL text is parsed or executed) and tables have a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Three entities with one text attribute each and two relationships; participation (optional vs mandatory) is not modelled, so foreign keys allow NULL. The one-to-one rule (UNIQUE foreign key on the second entity) is the common textbook mapping.
Loading the simulation…
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.
- 1Identify the one side
Customer is on the one side, so its primary key uniquely identifies each Customer instance.
- 2Identify the many side
Order is on the many side, so multiple Order instances can be associated with the one-side entity.
- 3Place the foreign key
The Order entity contains
customer_idas 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
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.
- 1Trace 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.
- 2Trace 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.
- 3Interpret 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
Common mistake Putting the foreign key on the one side
What it looks likeTreating Student as the many side of Student-to-Enrollment and placing the Enrollment reference there
Why it failsThe foreign key belongs on the many side and references the primary key on the one side.
Do this insteadIdentify the crow's-foot end first, then place the foreign key in that entity.
Common mistake Reading the crow's foot as the one end
What it looks likeInterpreting the three spreading lines as one instance
Why it failsCrow's Foot notation uses a vertical line for one and a crow's foot for many.
Do this insteadRead the simple vertical line as one and the spreading crow's-foot symbol as many.
Common mistake Treating the junction table as one side
What it looks likeAssuming Enrollment provides the primary key being referenced in both relationships
Why it failsIn the two one-to-many relationships, Enrollment is on the many side of each relationship.
Do this insteadPlace the foreign keys in Enrollment so they reference the primary keys of Student and Course.
Common mistake Ignoring referential integrity
What it looks likeAllowing a foreign key value that does not refer to an existing primary key value
Why it failsReferential integrity requires every foreign key value to refer to an existing primary key value.
Do this insteadCheck 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.
- 1A 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.