Instead of creating a seperated join table for one many-to-many relationship, I will design a centralized table, called 'Collection'. It will contain whole relationship data for all tables in database. This table like that:

enter image description here

  • Towards row 1, since table A and table B has many-to-many relationship, so the value of 'Unique_key' column will be NULL
  • Towards row 2 and 3, since table A and table C has one-to-many relationship, so there're multiple row value.

My question is: Compared to traditional design using a seperated join table for each of many-to-many relationships, by this way of using centralized table contains whole relationship data for all tables in database. Which way should should use?

  • The downside is that you usually end up with a significant amount of dynamic SQL and code which can be hard to read, or cursors or similar code that is less optimized. You could also end up with very large tables (at least larger than you would if you split them up and used a narrow N:N relationship table) that are difficult to index and query in general. There are other reasons which people smarter than I will probably come to explain. Having said that, I have had to do this a few times, but the requirements were not my own. I would choose linking N:N tables if at all possible. – Jacob H Jul 28 '18 at 1:21

You should definitely use the "traditional" way!

  • You cannot enforce referential integrity with constraints like that, as such a constraint has to point to only one table and cannot have multiple target tables depending on a columns value. You had to simulate it with complex triggers, that are almost sure to be less efficient than a simple foreign key constraints.

  • You won't be able to use "normal" indexes because of having everything in one table and with possibly the wrong type (I guess you intend Unique_key to be varchar?). Maybe there's some tricks around it with partial and function based indexes if (your version of) MySQL supports it (I don't know for sure at the moment). But even if such tricks were possible, again that's more effort than needed and likely to be less performant.

  • And maybe more...

Don't try to reinvent the wheel, especially not a triangular one...

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.