Many-to-many relationships and junction tables

Many-to-Many Requires a Third Table

A student can enroll in many courses. A course can have many students. Neither table can hold the relationship directly — you need a third table, the junction table, where each row represents one pairing.

-- Junction table for students and courses
CREATE TABLE enrollments (
  student_id  INTEGER REFERENCES students(id) ON DELETE CASCADE,
  course_id   INTEGER REFERENCES courses(id)  ON DELETE CASCADE,
  enrolled_at TIMESTAMP DEFAULT NOW(),
  grade       NUMERIC(4,2),
  PRIMARY KEY (student_id, course_id)  -- composite PK prevents duplicate enrollment
);

Extra Columns on the Junction Table

Junction tables often carry relationship-specific data — enrollment date, grade, role in a project. This is normal and expected. The junction table is a first-class entity.

Querying Many-to-Many

-- All courses a student is enrolled in
SELECT c.title, e.grade
FROM courses c
JOIN enrollments e ON c.id = e.course_id
WHERE e.student_id = 42
ORDER BY e.enrolled_at;

Always add indexes on both foreign key columns in the junction table. Queries almost always filter by one side or the other.