CODEWITHSUNDEEP
databasemany-to-manydata-modeling
Karan Gupta
Karan Gupta

Reputation: 529

how to model many 'many-to-many' relationships in rdbms?

I need to create a data model for an education based application. The question I want to ask is is it better to make one junction table for two tables with many-to-many relation or create one big junction table to deal with all many-to-many relationships?

Say, I have student, tutor, subject, grade tables.

student and tutor are in many-to-many
tutor and subject are in many-to-many
tutor and grade are also in many-to-many

A student can have many tutors for one subject of one grade.
There can be many tutors for one subject of one grade.
A subject of one grade can be taught by many tutors.

Above are just a few examples of the relationships.

My question is how to model these relationships efficiently? Should I have one junction table for each of the relationships or should I combine them into one big bridge table?

So, if I have a class table as well, then from the big bridge table I can get for which class which tutor taught which subject of what grade along with other details of the class.

Upvotes: 0

Views: 69

Answers (1)

Erwin Smout
Erwin Smout

Reputation: 18408

Let's assume the database is not yet electronic, but a good old filing cabinet instead. Let's assume the database is for a library, and there are a couple of distinct sorts of "many-to-many info" to be maintained : authors to books (coauthored books have >1 author), readers to books, readers to readers, book availability in possibly multiple site locations of the library, ...

Would you ever think of stashing all those distinct sorts of information in one big filing cabinet ? Imagine what the consequences are for its users ? Sometimes you'll be prohibited to do something "readers to books" merely because someone else is right there doing something "readers to readers". If and when you manage to gain access and it's finally your turn do so something, say "authors to books", your work will be slowed down because all the "readers to books" stuff might come in between and you'll have to spend extra time merely skipping the unneeded stuff. If a "conversion operation" must be performed, say, a new kind of many-to-many stuff is discovered and must be integrated in the single filing cabinet, the entire database is inaccessible while the conversion operation is being performed (people adding filing cards of a color that wasn't yet in use). Etc. etc. . Those undesirable properties carry over almost 1-1 to the electronic equivalent.

As someone else put it : don't be afraid of tables. It's what a DBMS is good at.

EDIT

Brief : just keep it at one table per fact type, and abstain from making (/trying to discover) geeky abstractions like "they're all just properties" / "they're all just some many-to-many-relation" / ... . They're geeky because an end user/business user will not "see" it. And thus there is no business value in making them.

Upvotes: 1

Related Questions