How do you model a many-to-many relationship, e.g. students and courses?

Options

1. Store a list of course ids in a single column of the students table
2. Create a junction table with two foreign keys, like enrollments (Correct option)
3. Duplicate the students table once for each course that exists
4. It cannot be modeled in a relational database at all

Answer

Create a junction (join) table enrollments(student_id, course_id) with two foreign keys

Why

A student takes many courses and a course has many students. A relational column cannot hold a list, so you create a third table in the middle.

enrollments(student_id, course_id, enrolled_at): each row links one student with one course. The pair (student_id, course_id) is usually its primary key, preventing duplicates.

The junction table can also hold data about the relationship (date, grade).

CREATE TABLE enrollments (
    student_id INT REFERENCES students(id),
    course_id  INT REFERENCES courses(id),
    enrolled_at DATE,
    PRIMARY KEY (student_id, course_id)
);

Remember

Many-to-many = junction table with two foreign keys.

Key terms

• Junction table
• Foreign key
• Primary key
• Table