Database DesignRelationships & ER diagramsLesson 20 / 69

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