0

I have a table, A, whose rows have a many-to-one relationship with several other tables. The rows in the other tables--B, C, D--each have many As. Each A relates exclusively to another table. For example you never have an A that relates to both a B and C.

Currently, the A table is represented as a "flattened" sum-type where each row has a nullable AId, BId, CId, & DId. The "parent" of any given row in A is determined by which one of these FK's in non-null.

This has been working fine so far. However, I have several new features to be implemented down the road which will also have many As.

My question is: is there a more extensible design than simply adding more columns to store FK's to these other tables?

Nizam
  • 4,569
  • 3
  • 43
  • 60
user1569339
  • 683
  • 8
  • 20
  • It's hard to say something useful without knowing more about your usecase. At first glance I would say it seems silly to have this monster table `A`. Why not create a child table for each `B`, `C` and `D`? Yes, it's more tables, but it adds to clarity and performance. – wvdz May 05 '15 at 20:42
  • possible duplicate of [How to implement polymorphic associations in an existing database](http://stackoverflow.com/questions/8895806/how-to-implement-polymorphic-associations-in-an-existing-database) – Gert Arnold May 05 '15 at 21:15

1 Answers1

2

You have a type of MUCK (massive unified code key)table, but only worse.

Instead of a table like this:

MUCK(aid, bid, cid, did, value1)

It would work better like this:

MUCK(table, id, value1)

The table is a,b,c, or d (or so on) and the id is the id within that table.

However, I really suggest you do some research on why MUCK and EAV tables can be a nightmare as you add requirements and features.

Here is a question I recently answered and a link to another that discusses why EAV (and even more, MUCK) is not always the best idea.

How to store custom entity properties in a relational database

Community
  • 1
  • 1
KingOfAllTrades
  • 398
  • 1
  • 11