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.
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…
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
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 |
Why a direct foreign-key design does not capture many-to-many connections
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.
- 1Start with the User record
Choose the user whose course enrollments you want to find.
- 2Match user_id in Member
Look up every Member row whose
user_idmatches that User record. - 3Collect course_id values
Each matching Member row represents one enrollment and supplies a
course_id. - 4Find the related Course records
Use those
course_idvalues 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
Common mistake Treating a many-to-many relationship as one-to-many
What it looks likeAdding only course_id to User when users can belong to many courses
Why it failsOne User row can point to only one course through that single foreign-key column.
Do this insteadCreate a junction table with one row for each user-course connection.
Common mistake Putting a list of related identifiers in one cell
What it looks likeTrying to store all course identifiers in one User row
Why it failsThe source model explains that a single cell can hold only one value, not a list of values.
Do this insteadRepresent each connection as its own row in the junction table.
Common mistake Omitting one of the two foreign keys
What it looks likeCreating Member with user_id but no course_id
Why it failsThe row would identify a user but not the other entity involved in the relationship.
Do this insteadInclude one foreign key for each original table.
Common mistake Confusing the junction table with one of the parent tables
What it looks likeTreating Member as the complete list of users or the complete list of courses
Why it failsMember stores connections; User and Course store the related entity records.
Do this insteadFollow 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.
- 1Name the intermediate table
Use a table representing the connection between employees and projects.
- 2Add the first foreign key
Add an employee identifier that references the Employee table.
- 3Add the second foreign key
Add a project identifier that references the Project table.
- 4Identify one relationship row
One row means one particular employee is connected to one particular project.
- 5Use 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
- 1Ask whether one record on the first side can connect to multiple records on the second side.
- 2Ask whether one record on the second side can also connect to multiple records on the first side.
- 3If both answers are yes, identify the relationship as many-to-many.
- 4Create a junction table to represent the connections.
- 5Add one foreign key for each original table.
- 6Use the pair of foreign keys as a composite primary key to identify each relationship.
- 7When 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.