The Many To Many Junction Table
A relational table cannot directly express a many to many relationship, because a single foreign key column points at only one parent. The solution is a junction table, also called a join, bridge, or associative table.
Consider students and courses. A student takes many courses and a course has many students. You create a third table, an enrollment table, with two foreign keys:
- One referencing the student
- One referencing the course
Each row in the junction table represents one pairing, such as student five is enrolled in course nine. The combination of the two foreign keys usually forms the composite primary key, which prevents duplicate pairings.
The junction table is also the natural home for relationship attributes, facts that belong to the pairing rather than either side. An enrollment date or a grade describes the link between a student and a course, not the student alone and not the course alone, so it lives on the junction row.
To query, you join through the bridge. To list a student courses you join the student to enrollment to course. This pattern scales to tags on posts, roles on users, and countless other webs.
Key idea
Resolve a many to many relationship with a junction table whose two foreign keys form a composite key and which can carry attributes of the link itself.