DataslopeMany-to-Many Relationships
When rows on both sides can connect to many rows on the other — and the junction table that makes it possible.
Some relationships do not fit "one row points to one other row." A student takes many courses; a course holds many students. This is a many-to-many relationship, and it needs a special structure — because no single foreign key can express it.
Why a foreign key is not enough
Picture trying to store this with one column. If students had a
course_id, each student could take only one course. If courses
had a student_id, each course could hold only one student. Neither
works, because both sides are "many."
Ada connects to two courses; Math connects to two students. The links cross over each other — you cannot capture that with a single column on either table.
The junction table
The solution is a third table that sits between the two, storing one row per connection. It is called a junction, bridge, or join table. Each of its rows holds two foreign keys: one to each side.
Read it as two one-to-many relationships pointing inward: one
student has many enrollments; one course has many enrollments. The
enrollments table turns a single, awkward many-to-many into two
ordinary, well-understood one-to-many links.
Building and querying it
Each row in enrollments says "this student is in this course."
To list real names, you join through the junction table to both
sides:
The query "walks" from enrollments out to both students and
courses, producing one readable row per connection. Notice the
junction's primary key is the pair (student_id, course_id) —
a composite key that prevents enrolling the same student in the same
course twice.
The junction table can hold its own facts
A junction row often carries extra columns about the relationship
itself — an enrolled_on date, a grade, a role. These belong
to the pairing, not to either table alone, so the junction table is
their natural home.
Answering questions from both directions
Because the structure is symmetric, you can ask in either direction just by changing what you group or filter:
Same three tables, different question — "students per course" instead of "courses per student." The junction table answers both without any change to the schema.
Check your understanding
Why can't a single foreign key represent a many-to-many relationship like students and courses?
Because foreign keys are not allowed between unrelated tables.
Because a single column can hold only one value, so it can represent "many" on only one side, not both.
Because foreign keys cannot reference primary keys.
Because PostgreSQL forbids more than two tables in a query.
What is the role of a junction (bridge) table?
To store a backup copy of both tables.
To store one row per connection, holding a foreign key to each of the two related tables.
To replace the need for primary keys.
To speed up queries by caching results.
In an enrollments junction table, where does a per-enrollment grade column most naturally belong?
In the students table.
In the courses table.
In the enrollments table, because the grade describes the specific student-course pairing.
In a brand-new fourth table by itself.