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 only takes a minute to sign up.
Sign up to join this community
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?
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.
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
