Database Design – Managing Many to Many Relationships

database-design

I am working on a conceptual model for a database. I have the following entities:

Student (id, name, date of birth, department)

Subject (id, name)

This is a many-to-many relationship, one student may be enrolled in many courses, each course has many students. From what I understand, as I should break this many-to-many relationship by introducing a new into a entity, say 'registered' which allows a one-to-one relationship or one-to-many relationship?

Is this correct design decision?

Best Answer

Generally a many-to-many relationship is done with a junction table, like so:

Student (id, name, date of birth, department) 

Course (id, name)

Student_Course (student_id, course_id)

You can either create an artificial key on the Student_Course table or create a primary key or unique index with both fields. student_id is a foreign key to student.id and course_id is a foreign key to course.id.

Note that the junction table need not be exactly two fields. You can include fields such as date_added or date_dropped so you could see when students have added and dropped courses.

Related Question