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

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

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

  • You want to use de-normalized structure? No one will forbid it. – Akina Jul 8 '20 at 8:59
  • yes it is but i was wondering is there any disadvantage in this situation – Jake Lam Jul 8 '20 at 9:00
  • 1
    You will have multiple jakes in your student table. For most people, they would think that is weird. Your Student table in the second design is really just the enrollment table. As to why we normalize, en.wikipedia.org/wiki/Database_normalization – Anthony Genovese Jul 8 '20 at 13:47

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.

  • I see , the last one you mentioned about redundancy in storing other attributes is a true downside. Thanks – Jake Lam Jul 12 '20 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.