Manage many to many relationship between "N" number of tables

[steven.p24]

Hi All,

I need help on database design which can manage many to many
relationship between "N" number of tables. for last 2 days i'm
thinking over this but not able to find a solution it will be great if
someone can help me.

Problem:

Say i have 3 tables A, B & C in my db. They can have Many to Many
relation ship with each other i.e. A <-> B, A<->C, B <->C. other than
3 junction tables how can i establish this relationship in my db?
i'm asking this because here i have taken 3 tables as example only..if
tomorrow a new table D is added to this DB (which also requires many
to many relation with other tables) i want to handle such scenario
without creating few more junction tables( A <-> D, B <-> D, C <-> D)
in my DB. i want some generic DB design which can manage many to many
relationship between any number of tables.

is such design really possible or i'm being crazy?

Cheers !

 

[Allen Browne]

Steven, you may need to be more specific about what you want here.

You probably do need junction tables between any tables involved in
many-to-many relations, so you can't really create these things dynamically.

An alternative might be to examine how similar the structure of tables A, B,
and C are. If they are similar, it may be possible to use a single table,
with an additional field to distinguish whatever the difference is. For
example, if the tables are Customers, Employees, and Vendors, you could
certainly use a single Clients table with a ClientTypeID field to indicate
which is which.

Or perhaps some of these tables contain groupings of each other? Again, it
may be possible to combine them:
http://allenbrowne.com/AppHuman.html

If the fields in A, B, and C are radically different, yet they all have a
related record in another table, consider subclassing. The core idea again
is a single main table with the primary key and any shared fields, and
related tables with a foreign key and the fields unique to that subtype.

The most generic approach is to use a table with 3 fields:
- what is it?
- what attribute is it?
- what value has the attribute?
While that's certainly not always the best approach, it is sometimes a
useful way to create very flexible relationships.

Hope something among that is of use.