Reputation: 79
I'm designing a database where I have a Course table and and Professor table. The business rules are :
1) A professor can teach many courses. (This is obvious).
However, I get stuck in the second rule.
2) On a given semester, a course can be offered on different schedules, each of those offerings can be taught by a different professor. For example a typical freshman course on calculus has about 100 students every semester. The course is divided into 4 sections, each has 25 students and a different professor.
I have decided to model it as follow:
Course
{
course_code
prof_id
course_name
Primary key(course_code, prof_id)
}
Professor
{
prof_id
prof_name
}
do you think this design is good? If not please provide some suggestions.
Upvotes: 0
Views: 2104
Reputation: 174
This is a Typically many-to-many connection where you instead of joining Course directly to Professor you would have a table maybe called schedule:
This table should have the following info:
Course
{
course_code
course_name
Primary key(course_code)
}
Professor
{
prof_id
prof_name
Primary key(prof_id)
}
Schedule
{
course_code
prof_id
Primary key(course_code, prof_id)
}
You can also add some timeslots to the scehdule table so you can see which time of the week the course are held
Upvotes: 3