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

On Jan 2, 5:50 pm, "steven...._at_googlemail.com" <steven...._at_googlemail.com> wrote:
> 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 !

First generic designs pave the road to performance hell. You want to really model real relationships in the ERD.

A many to many to many relationship is handled in a relational system by an intersection ( map ) table that has all 3 keys involved.

If you map it as one many to many to another many to many etc ... you end up with a circle. ( Isn't this called a ring of death ? sorry been a while ). This just isn't correct conceptually or in a database design.

I think the classic example is the old employer/job title/project model. Say you want to track effective dates ( begin and end ) for a multiple projects with multiple employers and multiple roles.

You need to have a map table that includes the key for employer, key for job title ( role ), anjd project along with beginning and ending dates for each row. That's the only way to do it correctly ( as far as I know ).

If you have a many to many to many to many relationship you need an intersection ( map ) table with all 4 keys. Maybe this isn't ERD design 101 but it's probably 201. Received on Fri Jan 04 2008 - 18:36:19 CST