Model a many-to-many relationship with a junction table
A student can take several courses, and a course can have several students: a many-to-many relationship. Instead of storing a list of course IDs in students, record one student/course pair per row in enrollments. Use three tables and two foreign keys to express the relationship and decide how to prevent duplicate enrollment.
K marks a primary key; * marks a required column. On small screens, scroll the diagram horizontally or select it to view at full size.
A DBML example you can import
Assume a student can enroll in a given course only once. The composite primary key (student_id, course_id) expresses that rule. enrolled_at belongs to the enrollment, not to the student or course alone.
Table students {
id bigint [pk]
name varchar(100) [not null]
}
Table courses {
id bigint [pk]
title varchar(160) [not null]
}
Table enrollments {
student_id bigint [not null]
course_id bigint [not null]
enrolled_at timestamp [not null]
indexes {
(student_id, course_id) [pk]
}
}
Ref: enrollments.student_id > students.id
Ref: enrollments.course_id > courses.idThe button opens a new document made from this code. Edit without signing up; your existing work is kept. To practice importing it yourself, copy the code and paste it into Import in the editor.
Start with this example →Replace many-to-many with two one-to-many relationships
Each enrollments row points to one student and one course. A student ID can appear with different course IDs, and a course ID with different students. students → enrollments and courses → enrollments are each one-to-many. Declare both foreign keys; column names alone do not enforce the relationships.
A composite primary key is not two separate UNIQUE constraints
The composite key prevents duplicate pairs. Student 1/course 10 and student 1/course 20 can coexist, but a second student 1/course 10 cannot. Making student_id alone UNIQUE would limit each student to a single course and change the intended model.
When you need a separate id
If other tables frequently reference an enrollment, you can use enrollments.id as the primary key. Keep student_id and course_id NOT NULL and add UNIQUE(student_id, course_id) to prevent duplicate enrollment. A new id alone does not enforce the business rule.
Revisit the key if repeat enrollment is allowed
This example does not permit retaking the same course. For enrollment by term, introduce course_offerings linked to a course and term, then make (student_id, offering_id) unique. If multiple attempts within one offering must be recorded, consider a separate attempt-history table.
Decide which entity owns each attribute
A student name belongs in students, the course title in courses, and enrollment time, grade or enrollment status in enrollments. Comma-separated course IDs or repeated course_1 columns make it difficult to apply a foreign key and attach attributes to each relationship. The junction table gives that information a home.
Try it: add an enrollment status
Open the example and add a status varchar(20) column to enrollments. Check that student_id and course_id together form the primary key and that both foreign keys reach the correct table. Define allowed status values and transitions separately for a real service, then back up the edited example as JSON.
Open the example and practice →What you can use for free today
Free currently supports DBML and Schema Studio JSON import and export, plus table and relationship editing. Sign in to save to the cloud and back up important work as JSON. Supported PostgreSQL/MySQL DDL imports, the PostgreSQL metadata query and PNG export are also free. SQL/SVG exports are coming soon. MySQL input is converted to the PostgreSQL document model; review warnings against the original.
See plans and storage limits →