You are designing a college database. How would you model students, courses, and enrollments?
Interview preparation resource from Gate Smashers.
Model Student and Course as separate entities and use Enrollment as a bridge (junction) table to represent the many-to-many relationship. Example schema: Student(student_id PK, name, email, ...), Course(course_id PK, title, credits, ...), Enrollment(student_id FK, course_id FK, semester, grade, enrollment_date, ...). Enrollment holds relationship-specific attributes (e.g., grade). For keys: (student_id, course_id, semester) can be a composite PK, or Enrollment can have an enrollment_id PK plus a uniqueness constraint to prevent duplicate enrollments.
Design overview
Student and Course are modeled as separate entities. Because a student can take many courses and a course can have many students, their relationship is many-to-many.
Introduce an Enrollment table as a bridge (junction) table to convert the many-to-many into two one-to-many relationships.
Table examples
Provide simple table structures that capture primary attributes and the enrollment relationship.
- Student: student_id PK, name, email, ...
- Course: course_id PK, title, credits, ...
- Enrollment: student_id FK, course_id FK, semester, grade, enrollment_date, ...
Relationship and attributes
Enrollment acts as the bridge table: one Student can have many Enrollment rows, and one Course can have many Enrollment rows.
Relationship-specific information (for example, grade) belongs in Enrollment because it applies to a particular student's enrollment in a particular course.
Keys and uniqueness
Decide how to identify Enrollment rows based on requirements. Two common approaches are described below.
If you need to prevent duplicate enrollments, enforce uniqueness either via a composite primary key or a uniqueness constraint.
- Composite key option: (student_id, course_id, semester) could form a composite primary key.
- Surrogate key option: Enrollment could have its own enrollment_id primary key plus a uniqueness constraint to prevent duplicate enrollments.