How to represent a relationship of cardinality many to many

database-designerdmany-to-many

I'm trying to design a database for the information shown in the table
below:

In this regard, let's make the following assumptions:

Each Student may take many Courses
Each Course may have many Students
Student Numbers, Course Numbers and Advisor Names are unique
Student Names and Course Descriptions are not unique
Each Student has one Advisor
Each Advisor may advise many Students
Each Advisor has one Office
A Student can visit with his Advisor many times

My current approach

So far, I have come up with the following diagramatic representation:

The question

Being that this contains a many-to-many (M:N) relationship, I am confused as to what the association class (or associative entity type) should be.

What would the correct way to represent this relationship be?

Best Answer

You could have a Course table which would just contain the courses and descriptions.

As stated in the comments, you could then have a StudentCourse table which had either a surrogate PK, with foreign keys to Student and Course or a composite PK (StudentNumber and CourseNumber) with the Grade for the student held in it.

Similarly, you could have an Office table and link the Office back to the Advisor. This would allow multiple advisors to have the same office without having to hold the office name multiple times (what if the office name changed for example?).

As also mentioned using the Advisor name as the primary key might not be best, their name could change; you might be better to use a Surrogate key (integer perhaps?) for that.

Related Solutions

Database Design – Managing Many to Many Relationships

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.

Database Design – Best Design for Storing Course Subscription Data

Both are OK designs, so now the problem is deciding between the two. Here are some things that I think you should consider as you decide which one is best.

How similar are the non-essential columns in the Course Subscription and Lesson Subscription tables? If they are very similar then records which contain only a course subscription in the Subscriptions table would not have a bunch of null columns to skip which apply only to lesson subscriptions and vice versa. The more similar the columns are, the more you should lean towards using the Subscriptions design.
Can a student subscribe to both a course and a lesson at the same time? If the student does that, will the data for any shared columns between a course and lesson be different? If the data is different, then you'll definitely need the CourseSubscriptions and LessonSubscriptions tables. Otherwise, you should be fine with just a Subscriptions table.

Finally, if you go with one table... Think through how your output would handle a combined course and lesson subscription. Is that two subscriptions or one? Conversely, if you go with two tables and one action results in two entries, is that one or two?

Best Answer

Related Solutions

Database Design – Managing Many to Many Relationships

Database Design – Best Design for Storing Course Subscription Data

Related Question