CODEWITHSUNDEEP
mysqldatabase-designrelation
rkmax
rkmax

Reputation: 18133

Single Relation M:N for unknow element number

Currently I have an article with various M:N relations with other elements, the problem is that these elements can and will grow up and not want to have that amount of tables in my database.

enter image description here

which is the right way to build a single relationship M: N for an unknown number of elements

Upvotes: 2

Views: 71

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

Which is the right way to build a single relationship M: N for an unknown number of elements

There is nothing particularly wrong with having separate junction table for each of the M:N relationships.

That being said, you can handle these relationships in a more generic way, and at the same time lower the number of tables by using inheritance1:

enter image description here

For more information about the concept of inheritance, search for "Subtype Relationships" in ERwin Methods Guide. For some hints at how the inheritance can be implemented in a relational database, take a look at:

1 Aka. category, subclassing, subtyping, generalization hierarchy...

Upvotes: 1

Gustek
Gustek

Reputation: 3760

If all elements would have same attributes You could keep them all in one table with extra column(enum best) to distinguish between them.

But looking at image You provided they probably don't have same attributes so it would be difficult and inefficient to somehow force them to be in one or just few tables.

There is no limit on number of tables You may create and I have no idea why should You avoid creating a new table for each relation as it should be.

Upvotes: 0

Related Questions