2

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

Sebas
  • 21,192
  • 9
  • 55
  • 109
rkmax
  • 17,633
  • 23
  • 91
  • 176
  • Why not having these tables in your database? Apparently your fonctional environment limits the magnitute by itself; I don't really see you having more than a few more tables than these (~ 40/50 is not so much). Don't forget that denormalising has a price. – Sebas Apr 14 '13 at 23:47
  • I mean I do not want lots of tables category, article_category, author, article_author, other, article_other when this tables has the same structure (id, name) – rkmax Apr 15 '13 at 00:06
  • you could make one table then, (article_id, object_id, object_name), object_name being the name of the table your relate the article to – Sebas Apr 15 '13 at 00:09
  • @rkmax: one of my database programs started like this - I had several tables, each denoting a different entity, all of which initially had two fields, id and name. But as time went on, I needed extra functionality and so most tables had fields added. I think it's best to have different tables, even though they apparently have the same structure. – No'am Newman Apr 15 '13 at 05:02

2 Answers2

1

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...

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
0

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.

Gustek
  • 3,680
  • 2
  • 22
  • 36