DataslopeMany-to-Many Relationships
Learn why SQLite many-to-many relationships need a junction table and how to join through it.
Some relationships are not one-to-many. A student can take many courses, and a course can have many students. That is a many-to-many relationship.
Ada connects to more than one course. Math connects to more than one student. A single foreign-key column on either table cannot describe all those crossings.
Why one foreign key is not enough
If you put course_id on students, each student could store only one course. If you put student_id on courses, each course could store only one student. Both designs lose information.
Why can't a single course_id column on students represent students and courses correctly?
SQLite does not allow integer columns.
One column value can point to only one course for each student row.
Course names are too long.
Primary keys cannot be joined.
The junction table
The solution is a third table between them. It is often called a junction table, bridge table, or join table.
Each row in the junction table stores one connection:
student 1 is enrolled in course 10
student 1 is enrolled in course 11
student 2 is enrolled in course 10Read the diagram as two one-to-many relationships:
- one student can have many enrollment rows
- one course can have many enrollment rows
The many-to-many relationship becomes two ordinary one-to-many relationships meeting in the middle.
Build the three tables
The primary key is the pair (student_id, course_id). That means the same student cannot be enrolled in the same course twice.
Join across the bridge
To show names and course titles, start at enrollments and join outward to both parent tables.
Ask questions from either direction
Because the junction table stores links, you can ask student-focused or course-focused questions without changing the design.
Junction tables can hold facts about the relationship
A value like grade or enrolled_on belongs on enrollments, because it describes a particular student-course pairing. Ada's grade in Math is not just a fact about Ada, and it is not just a fact about Math. It is a fact about Ada taking Math.
Naming junction tables
Use a name that describes the relationship: `enrollments`, `memberships`, `order_items`, `book_authors`. The table is not just plumbing; it represents real connections.
Check your understanding
What is a junction table?
A copy of two complete tables pasted together.
A table that stores one row per connection between two related tables.
A table with no primary key or foreign keys.
A table used only for sorting results.
In enrollments(student_id, course_id), what does one row represent?
One student by themselves.
One course by itself.
One student-course connection.
Every student in every course.
Why use PRIMARY KEY (student_id, course_id) on enrollments?
It makes every student take exactly one course.
It prevents the exact same student-course pair from being inserted twice.
It prevents a course from having many students.
It hides the table from joins.
Where should a grade for Ada in Math be stored?
On Ada's student row only.
On the Math course row only.
On the enrollment row connecting Ada to Math.
In both students and courses as copied text.