How to design SQL tables to allow for multiple parent table options?

Question

Looking into creating a series of tables in a reporting hierarchy and am more or less drawing a blank.

While tables in this structure can only have one parent, how do I structure the fields so that they point to the right parent table? As you'll see in my example below, a row's parent table can differ.

                  ARRANGEMENT
                  /           \
           MATTERS            ISSUES 
              |                     |
            PHASES                MATTERS
          /       \                 |
     ISSUES      TASKS            PHASES
     /    \        |             /      \
 TITLES  TASKS   ISSUES       TASKS    TITLE  
           |        |           |
        TITLES   TITLES       TITLE

Essentially, is it best to have each "branch" have a unique table (even though Tasks in branch 1 has the same data structure as branch 2 or 3), or is it best to have the records identify which table is their parent?

Arrangement(ID)
Matters(ParentTable, ParentID, ID)
Phases(ParentTable, ParentID, ID)
Issues(ParentTable,ParentID, ID)
Titles(ParentTable,ParentID, ID)
Tasks(ParentTable,ParentID, ID)

The above doesn't seem right to me at all. Help?

Answer 1

You could have 1 table with ID & ParentID.
The top level row (Arrangement) will have NULL ParentID.

Answer 2

You can't really do that - at least not in any RDBMS I know of, if you use referential integrity (foreign key relationships), since those always have to reference one and exactly one parent table - you cannot have a FK relationship that reference parent table A in one case and parent table B in another.

In your concrete case, where a "Title" could be child of both "Phases" or "Tasks", one way to solve this would be to have a "dummy" Task for those "Title" entries that should be direct children of the "Phases" table.

Anything else will be a hack and a nightmare to maintain in the long run.

Marc

Answer 3

I have seen polymorphic associations of various sorts (not just parent relationships) handled that way in relational databases. I'd say go ahead and use the `ParentTable, ParentID' approach.

The downsides are that you won't be able to enforce referential integrity at the database level (ie, use foreign keys), and it's going to be a little more work to fetch association unless you're using a framework that will do the legwork for you (eg, Rails). If you really need polymorphic associations, though, I don't know any good way around those complications.

Answer 4

I have two opinions. You must be clear on the meaning of these "polymorphic" entities. Are they semantically different and separate? Even if they look the same, you may not want to put them into the same table if they serve different purposes.

---

If a Matter is truly, semantically fungible between Arrangements and Issues, then I'd suggest using a form of "Mapping" tables:

Arrangement(ID)
Matters(ID)
Issues(ID)
ArragementMatters (FK_ArrangementID, FK_MatterID)
IssueMatters (FK_IssueID, FK_MatterID)

You can continue this pattern throughout the "polymorphic" tables.

You can add a unique constraint on FK_MatterID columns if required.

It's easy to write queries:

Select * from Arrangement a 
inner join ArrangementMatters am on am.FK_arrangementID = a.ID
inner join Matters m on m.ID = am.FK_matterID

to get all your Matters associated with Arrangements.

---

On the other hand, if a Matter under and Arrangement is NOT semantically fungible and only has the same schema, then I'd suggest creating completely separate tables:

Arrangement(ID)
ArrangementMatters(ID, FK_ArrangementID)
Issues(ID)
IssueMatters(ID, FK_IssueID)

This conveys the distinction to the world. It gives you a lot of benefits if you can separate your concerns. (i.e. Maybe you have lots of heavy usage of IssueMatters vs. ArrangementMatters - you can optimize the indexes and table layout independently.)

A query is also simpler:

Select * from Arrangement a 
inner join Matters m on m.FK_arrangementID = a.ID