Primary Keys and Foreign Keys: Building Connections Between Tables
A many-to-many relationship exists when records on both sides can connect to multiple records on the opposite side, unlike one-to-many where multiplicity flows in only one direction.
When One-to-Many Falls Short
A one-to-many relationship works when one record connects to multiple records of another type, while each record on the many side connects back to one record on the other side. A teacher can have many students, and a department can have many employees. However, some real-world connections work in both directions: one record can connect to many records on the opposite side, and each of those records can also connect to many records on the first side.
Before choosing a table structure, ask two questions: Can one record on the left connect to multiple records on the right? Can one record on the right connect to multiple records on the left? If both answers are yes, the relationship is many-to-many.
Reading Cardinality in an ER Diagram
Cardinality describes how many records on one side can be connected with records on the other side. In an entity-relationship diagram, symbols appear at the ends of the line connecting two tables. A one-to-many relationship has a single indicator at one end and a crow's foot, or many indicator, at the other. A many-to-many relationship has a many indicator at both ends.
| Relationship | Multiplicity at the first side | Multiplicity at the second side | Meaning |
|---|---|---|---|
| One-to-many | One | Many | One record connects to multiple records of the other type. |
| Many-to-many | Many | Many | Records on both sides can connect to multiple records on the opposite side. |
Cardinality patterns described in the source material.
Testing the User-Course Relationship
Classifying a User and Course Model
Determine whether the relationship between users and courses is one-to-many or many-to-many.
Check the user side: A user can belong to many courses, so one user can connect to multiple course records.
Check the course side: A course can contain many users, so one course can connect to multiple user records.
Compare both directions: Multiplicity exists at both ends. The relationship is therefore many-to-many rather than one-to-many.
Users and courses form a many-to-many relationship.
This classification step should happen before choosing where to place foreign keys. If only one side can connect to multiple records, a foreign key on the many side can represent the relationship. If both sides can connect to multiple records, a single foreign-key column on either original table is not enough.
Why Direct Foreign Keys Fail
A one-to-many relationship can be implemented by adding a foreign-key column to the many side. For example, a student table can have a teacher_id column, so each student record points to one teacher. The same pattern cannot directly represent users belonging to many courses. If the user table receives a course_id column, each user row can point to only one course. If the course table receives a user_id column, each course row can point to only one user. Neither arrangement represents multiplicity in both directions.
The Junction Table Solution
A junction table, also called an associative entity, resolves a many-to-many relationship by introducing a third table. This table contains foreign keys pointing to both original tables. Instead of trying to connect users directly to courses in one column, the junction table stores connections between a user and a course. The original many-to-many relationship is thereby broken into two one-to-many relationships.
Breaking One Many-to-Many Relationship into Two One-to-Many Relationships
Represent users belonging to many courses and courses containing many users.
Create the two original tables: Keep User and Course as separate tables representing the two types of records.
Add the junction table: Create an associative entity between them. It contains a foreign key pointing to the User table and another foreign key pointing to the Course table.
Read the first relationship: One user can be connected to many records in the junction table, producing a one-to-many relationship.
Read the second relationship: One course can also be connected to many records in the junction table, producing a second one-to-many relationship.
Recover the original meaning: Following the two relationships allows any user to connect to multiple courses and any course to connect to multiple users.
The junction table represents the many-to-many relationship as two manageable one-to-many relationships.
Design Checks and Common Mistakes
Treating every relationship as one-to-many
The user-course scenario allows a user to belong to many courses, and a course to contain many users.
Fix:
Test multiplicity in both directions before selecting the relationship pattern.Adding one foreign key to the wrong original table
A foreign-key column stores only one value per row, so one course row would point to only one user.
Fix:
Use a junction table with foreign keys to both original tables.Missing the ER-diagram signal
Many indicators at both ends represent many-to-many cardinality.
Fix:
Look at both ends of the connecting line: one many indicator means one-to-many; many indicators at both ends mean many-to-many.Waiting until implementation to identify the relationship
Changing the model later can require significant rework.
Fix:
Ask the two multiplicity questions during the design process.
Identify many-to-many relationships early. For each proposed connection, check multiplicity from the left side and then from the right side. When both directions are many, plan for an associative entity rather than trying to place the relationship in one direct foreign-key column.
Practice the Recognition Test
A design has two tables, Authors and Books. One author can be connected to multiple books, and one book can be connected to multiple authors. Identify the relationship type, describe how it would appear in an entity-relationship diagram, and state whether a direct foreign key in either original table is sufficient.
Hints
- Check whether multiplicity exists in both directions.
- A many-to-many relationship has a many indicator at both ends of the ER-diagram line.
- A direct foreign-key column stores only one value per row; consider the role of a junction table.
Key Takeaways
- A one-to-many relationship has multiplicity in one direction; a many-to-many relationship has multiplicity in both directions.
- An ER diagram shows a many-to-many relationship with many indicators at both ends of the connecting line.
- A foreign-key column can store only one value per row, so a direct foreign key cannot represent a many-to-many relationship.
- A junction table, also called an associative entity, contains foreign keys to both original tables.
- The junction table changes one many-to-many relationship into two one-to-many relationships.
Key Takeaways
- Use multiplicity in both directions to recognize a many-to-many relationship.
- In an ER diagram, many-to-many relationships have many indicators at both ends of the connecting line.
- A single foreign-key column stores one value per row and cannot directly represent multiple connections in both directions.
- A junction table resolves the problem by storing foreign keys to both original tables.
- The junction table creates two one-to-many relationships that together model the original many-to-many connection.