Many-to-many & join tables
Exercise30 min
Many-to-many & join tables
Students take many courses; courses have many students. You cannot put a column on either side without lying about the data. The answer is a join table that turns one many-to-many into two one-to-manys:
CREATE TABLE enrollments (
student_id bigint REFERENCES students(id),
course_id bigint REFERENCES courses(id),
enrolled_at timestamp NOT NULL DEFAULT now(),
PRIMARY KEY (student_id, course_id)
);The composite primary key says "a pair can occur only once" — the same student cannot enroll in the same course twice. Join tables are also where relationship data lives, like enrolled_at.
Exercise
Design movies ↔ actors with a join table that also stores the character name. Write the CREATE TABLE.
Practice in the diagram lab
This lesson's practice belongs in the diagram editor — design the schema you just read about, grade it against the solution, and save it to your account.
Open the diagram lab