Many-to-many, and the table in the middle

The relationship that needs its own table

A student takes several courses. A course has many students. There is no column you can add to either table that holds this, because a column holds one value and you need many.

The answer is a third table whose rows are the relationship:

CREATE TABLE enrolments (
  student_id  bigint NOT NULL REFERENCES students(id),
  course_id   bigint NOT NULL REFERENCES courses(id),
  enrolled_on date   NOT NULL DEFAULT current_date,
  status      text   NOT NULL DEFAULT 'active',
  PRIMARY KEY (student_id, course_id)
);

This is variously called a bridge, junction, join or associative table. The name does not matter. What matters is that it is a real table with a real grain (one row per student per course) and it can carry its own columns, which is the part people underuse. The date somebody enrolled, whether they dropped out, what they paid: all of these are facts about the relationship, not about the student or the course, and this is the only correct place for them.

Querying across it

Two joins, in order:

SELECT s.full_name, c.title, e.enrolled_on
FROM students s
JOIN enrolments e ON e.student_id = s.id
JOIN courses    c ON c.id = e.course_id
WHERE c.title = 'Statistics I';

Grain check: the result is one row per enrolment, which is one row per student per course. A student on three courses appears three times. That is correct and expected, and it is exactly the multiplication that ruins any per-student total you compute on top of it without grouping first.

Signs the bridge table is missing

You will meet data where somebody avoided the third table. Two symptoms:

A comma-separated column. students.courses holding 'stats,algebra,physics'. Every question becomes a string search. WHERE courses LIKE '%stats%' also matches a course called 'biostats'. Counting how many students take algebra requires counting commas. Adding a course means rewriting a string in every affected row, transactionally, without race conditions, which nobody does correctly.

Numbered columns. course1, course2, course3. The moment somebody takes a fourth, you are altering the schema, and every query has to be rewritten to check three columns instead of two. Ask “how many students take Statistics I” and you need WHERE course1 = 'stats' OR course2 = 'stats' OR course3 = 'stats'.

Both are the “no repeating groups” rule from the first module, wearing different clothes. The fix in both cases is to unpack them into a bridge table once and never think about it again.

Counting on both sides

Because the bridge table holds the relationship, both directions are the same shape of query:

-- students per course
SELECT c.title, count(*) AS students
FROM courses c JOIN enrolments e ON e.course_id = c.id
GROUP BY c.title;

-- courses per student
SELECT s.full_name, count(*) AS courses
FROM students s JOIN enrolments e ON e.student_id = s.id
GROUP BY s.full_name;

Note that both of these silently exclude students with no courses and courses with no students, because an inner join drops unmatched rows. If the report is about coverage, which courses nobody signed up for, you need a LEFT JOIN from the side you care about and count(e.course_id) rather than count(*), since count(*) would count the single NULL-filled row as one.

That distinction, count(*) versus count(column) after a left join, is the most common way an “empty” category is reported as having one member.

Two useful patterns

”Students taking both A and B”: a question that trips people up because no single row contains both:

SELECT student_id
FROM enrolments
WHERE course_id IN (11, 22)
GROUP BY student_id
HAVING count(DISTINCT course_id) = 2;

Group and count, rather than trying to join the table to itself. This generalises: change the 2 and the list and it answers “all of these”, for any number.

”Courses with no students”: the anti-join:

SELECT c.* FROM courses c
LEFT JOIN enrolments e ON e.course_id = c.id
WHERE e.course_id IS NULL;

One design note

Whether the bridge table gets its own surrogate id, in addition to the composite primary key, is a real argument with no universal answer. A composite key states the rule that the pair is unique and is self-documenting. A surrogate id makes the table easier to reference from a fourth table and easier for some application frameworks to handle. Pick one, and if you add a surrogate id, keep a UNIQUE constraint on the pair: otherwise the rule you were relying on is gone and duplicate enrolments will appear.