Junction Tables: Implementing Many-to-Many Relationships
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 can connect to many records, but each record on the many side connects back to only one record. For example, a teacher can have many students, and a department can have many employees. Some real-world connections work differently: a student can enroll in many courses, while each course can contain many students. When both sides can connect to multiple records on the opposite side, the relationship is many-to-many.
What do you think happens?
A student can take several courses, and each course can have several students. Which relationship pattern is this?
Reveal answer
Answer: Many-to-many
Multiplicity exists in both directions: one student can connect to multiple courses, and one course can connect to multiple students.
Why Two Foreign Keys Are Not Enough
A one-to-many relationship is straightforward because the table on the many side can contain one foreign-key column pointing to the table on the one side. For example, a student table can contain teacher_id, so each student record points to one teacher. That arrangement cannot represent a many-to-many relationship by itself. If a user table receives a course_id column, each user row can point to only one course. If a course table receives a user_id column, each course row can point to only one user. Neither column can hold all of the related values needed by the scenario.
| Design attempt | What one row can store | Problem |
|---|---|---|
| course_id in User | One course identifier for a user row | The row cannot represent all courses for that user |
| user_id in Course | One user identifier for a course row | The row cannot represent all users for that course |
| Separate relationship rows | One user-course connection per row | Represents every connection without putting a list in one cell |
The Junction Table Pattern
A junction table, also called an associative entity, is an intermediate table that resolves a many-to-many relationship. It contains a foreign key for each of the two original tables. The junction table changes one many-to-many relationship into two one-to-many relationships: each record in either original table can connect to many rows in the junction table.
For students and courses, the source model uses a Member table. Member contains user_id, which references the User table, and course_id, which references the Course table. Each Member row represents one specific enrollment. Several rows can use the same user_id to represent one user's several courses, and several rows can use the same course_id to represent a course's several users.
Keys That Identify Each Connection
The junction table has at least two columns: one foreign key for each connected table. In the User, Course, and Member example, user_id references the primary key of User, and course_id references the primary key of Course. The combination of user_id and course_id typically forms a composite primary key. Together, the two values identify one relationship: one particular user connected to one particular course. This prevents the same relationship from being treated as a separate duplicate connection.
| Member column | Role | References |
|---|---|---|
| user_id | Foreign key | Primary key in User |
| course_id | Foreign key | Primary key in Course |
| user_id + course_id | Composite primary key | Uniquely identifies the relationship |
The minimum conceptual structure of the Member junction table
Tracing an Enrollment
Finding a Student's Courses
Trace how the database finds all courses connected to one user through the Member table.
Start with the User record: Choose the user whose course enrollments you want to find.
Match user_id in Member: Look up every Member row whose user_id matches that User record.
Collect course_id values: Each matching Member row represents one enrollment and supplies a course_id.
Find the related Course records: Use those course_id values to look up the corresponding records in Course.
The Member table acts as the bridge between one User record and all of its related Course records.
The same bridge works in the opposite direction. To find all students in one course, start with the Course record, find Member rows with the matching course_id, collect their user_id values, and then retrieve the corresponding User records. The junction table is therefore not merely an extra table; it is the place where each individual connection is stored and later followed.
Design Mistakes to Avoid
Treating a many-to-many relationship as one-to-many
One User row can point to only one course through that single foreign-key column.
Fix:
Create a junction table with one row for each user-course connection.Putting a list of related identifiers in one cell
The source model explains that a single cell can hold only one value, not a list of values.
Fix:
Represent each connection as its own row in the junction table.Omitting one of the two foreign keys
The row would identify a user but not the other entity involved in the relationship.
Fix:
Include one foreign key for each original table.Confusing the junction table with one of the parent tables
Member stores connections; User and Course store the related entity records.
Fix:
Follow the foreign keys from Member to retrieve the parent records.
Practice the Pattern
A company has projects and employees. One employee can work on many projects, and one project can have many employees. Describe the junction table you would introduce, name its two foreign-key columns, and explain what one row in that table represents.
Hints
- Check whether multiplicity exists in both directions.
- Use one foreign key for each original table.
- Think of one row as one employee-project connection.
Checking the Answer
Model the employee-project relationship using a junction table.
Name the intermediate table: Use a table representing the connection between employees and projects.
Add the first foreign key: Add an employee identifier that references the Employee table.
Add the second foreign key: Add a project identifier that references the Project table.
Identify one relationship row: One row means one particular employee is connected to one particular project.
Use the pair as the composite key: The employee identifier and project identifier together identify that connection.
The intermediate table changes the many-to-many employee-project relationship into two one-to-many relationships and stores each connection separately.
Design Checklist
- Ask whether one record on the first side can connect to multiple records on the second side.
- Ask whether one record on the second side can also connect to multiple records on the first side.
- If both answers are yes, identify the relationship as many-to-many.
- Create a junction table to represent the connections.
- Add one foreign key for each original table.
- Use the pair of foreign keys as a composite primary key to identify each relationship.
- When tracing data, find matching junction rows first and then follow their foreign keys to the related records.
Key Takeaways
- A many-to-many relationship exists when records on both sides can connect to multiple records on the opposite side.
- An entity-relationship diagram shows this pattern with many indicators at both ends of the relationship line.
- A single foreign-key column cannot store all the connections required by a many-to-many relationship.
- A junction table resolves the pattern by storing one foreign key for each original table and turning the relationship into two one-to-many relationships.
- The two foreign keys typically form a composite primary key, and data is traced by finding junction rows before retrieving related parent records.