Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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

1 Answer 1

up vote 10 down vote accepted

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.

share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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