1

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?

|improve this question
  • take a look at this wiki. It will help you to understand basics – Biju jose Dec 3 '12 at 4:08
11

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.

|improve this answer
  • Also both the foreign keys should be part of primary key so that no record gets duplicated and it also helps performance when getting data back. – codecool Dec 3 '12 at 7:33
  • Usuallly, but it depends on the use case. If you need to support multiple possible many-to-many relationships (such as allowing a class to be added and dropped multiple times) then you can't do that, obviously. In the Student Information System I work on, for example, there's a modeled flag column that dictates whether the schedule is an actual schedule, or just one that is being created. The system also supports multiple school districts, so the actual primary key on this production junction table is ([DISTRICT], [STUDENT_ID], [SECTION_KEY], [MODELED]). – Bacon Bits Dec 3 '12 at 14:18
  • Agreed, it depends on the use case. I just mentioned it so that op gets a clear picture of this type of relationship. :) – codecool Dec 3 '12 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.