CODEWITHSUNDEEP
databasedatabase-design
Joe Jean JJ
Joe Jean JJ

Reputation: 79

Relationship between Professor table and Course table

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

Answers (1)

Yao Bo Lu
Yao Bo Lu

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

Related Questions