CodePark/ questions / Schema design & normalisation
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Schema design & normalisation

Students can enrol in many courses, and courses have many students. How should you model this in a relational database?

A junction table enrollments(student_id, course_id) with foreign keys to both tables and a primary key on the pair

A many-to-many relationship needs a junction table. A junction table has one row for each student-course pair.
• Foreign keys to both tables enforce integrity, so a row can't point to a missing student or course.
• A composite primary key on the pair prevents duplicate enrolments.
• It can also store facts about the relationship itself, such as the enrolment date.

"A course_id column on students" allows only one course per student. "A comma-separated list of course ids" and "A student_ids array column" both lose foreign key enforcement, so the database can't check the ids.