Parent & Child FK'ing to same table

Question

What is best practise when a parent & child table both FK to the same table?

Parent > Child(ren) 

CommonAttributes: Sex, Age, Height, Weight

Is it better to directly reference the common table:

CommonAttributes > Parent(s) > Child(ren)

&

CommonAttributes > Child(ren)

Or use a reference table:

RefTable: CommonAttributes_Id, Parent_Id(null), Child_Id(null)

I think the first method works OK (with regards to EF) but it is a bit of a circular reference. Is it better to use a reference table to define the constraints?

Answer 1

There are several approaches to this and the one you need depends on your business needs.

First, can a child record have more than one parent? For instance you might be modelling an organizational structure where an employee can have two supervisors. If this is true, then you have a one to many relationship and need a separate table for this model to work.

If you are guaranteed to have only one parent per child (but each parent might have a parent (building a hierarchy), then you can model this is one table. The table structure would include the Primary key, say UserID and then a nullable column for the parent such as ParentUserID. Then you can create the foreign key to the field in the same table.

ALTER TABLE dbo.Mytable  ADD CONSTRAINT FK_Mytable _UserPArent FOREIGN KEY (ParentUserD)      REFERENCESdbo.Mytable (UserID)  

If you want to build a hierarchy in a query, you then use a recursive CTE to get it. See example here: https://msdn.microsoft.com/en-us/library/ms186243.aspx

Another time you might want to build a separate table for the child parent relationship is if only a small portion of teh records in the main table would have parent child relationships. For instance suppose you had a people table that stored, sales reps and customers. Only sales reps would have a parent child relationship. So you would want a separate SalesRepHierarchy table to store it which woudl make querying more straightforward.

While in general you woudl want to create hierarchies in a recursive CTE, there are special cases when it might be faster to pre calculate the hierarchies. This is true if the hierarchy is frequently queried, the CTE performance is slow and you have control over how the hierarchy is built (preferably through an import of data only) and if it changes fairly rarely (you would not want to be rebuilding the hierarchy every minute, but a once a day import can be accommodated. This can greatly speed up and simply querying for the whole hierarchy, but is not recommended if the parent child relationships are created and changed constantly through the application.