Is it better to omit associative tables in displaying many to many relationship

database-design

Let's say I have 2 entities Student and Class with many to many relationships. Usually, in the textbook, it is recommended that we create another associative table (maybe called Enrollment) to convert many-to-many relationships becoming 2 one-to-many relationships.
enter image description here

So this design above is correct and I have no problem with it.

However, I also thinking of a more simple design like this

Student
id name class_id
1  Jake 1
2  Jake 2
3  John 1


Class
id name
1  Math
2  English
3  Physics

And I think it can also work fine without even creating the third tables. (and only Student table has the foreign key)

So my question is what are the pros and cons of the second method ( without associate tables). Is there any particular case that making the text-book method (1st solution) better than the 2nd solution and vice versa. Thanks

Best Answer

Your proposed Student table is the same as the picture's associative table, only using the natural key (Student Name) instead of the surrogate key (Student ID) as the primary key. (As an aside, getting a natural key that uniquely identifies humans is one of the notoriously hard tasks in data.)

The downside of your proposed model is that the student can have no existence outside of their participation in a class. The student comes into existence (as far as this model is concerned) only when she enrolls in a class. Should she un-enroll she ceases to participate in this model in any way whatsoever. I know this is a made-up teaching example, but in real life we'd have students who are not currently enrolled in any class - say from the time they're accepted until they show up days or months later and enroll. Further each student will have many more attributes (phone number, next of kin, etc.) In your proposed solution these must be stored redundantly on every row. This breaks second normal form, risking data update anomalies.

Related Question