CODEWITHSUNDEEP
databasedesign-patterns
peidiam
peidiam

Reputation: 207

A better db design for 1-to-many-to-many relationship?

I currently encounter a problem and wonder if there exists a better solution for this case. Initially I have three entities, A, B, and X. Their relationships are as follows.

For example, one row in A may associate with many rows in both B and X. Also, one row in B may have many other associated rows in X.

Some of the options I have considered:

  1. Duplicate X

    So we have A-B, A-X1, and B-X2 in which X1 and X2 are identical, just different table name.

  2. Use polymorphic association

    X now has two columns parent and parent_id indicating with which table, A or B, a row is associated.

  3. Reverse the polymorphic association

    Two additional tables, AX and BX, are created. The relationship is straightforward.

    A--AX--X--BX--B

  4. Use an additional foreign key for X.

    X now has two columns, A_id and B_id. If a row is associated with A, then B_id is null. If a row is associated with B, then A_id = A.id and B_id = B.id.

Of course they all have tradeoffs. I wonder if there are any better solutions for this problem. Please suggest any one you know.

Thanks!

Upvotes: 1

Views: 131

Answers (1)

Paul Sasik
Paul Sasik

Reputation: 81507

I think #3 is your best option. The common db modelling term for that kind of relationship is Junction Tables. It is a very clean way for creating many-to-many relationships between tables. And as you said:

The relationship is straightforward.

With junction tables you don't need to use duplicates (#1) or create flags indicating which table to link it to (#2) and #4 looks like a slightly different take on #2.

Many db modelers will bristle at the extra joins but as long as your indexes are set properly the model will be performant. And if you get tired of writing out joins, create some views.

Upvotes: 1

Related Questions