I'm dealing with a set of relationships where the child can be removed, but I obviously don't want to lose the connection between the grandchild and the parent. I did think about marking the child as "deceased" (to use relevant terminology for this post), but then I would be stuck with a bunch of deceased children in my DB eventually and who wants that (just for the purpose of preserving the relationship)?
If a parent is deleted, all its descendants are deleted. Furthermore, it works like a "normal" relationship, where the grandchild and child always have the same top level parent. The hierarchy is fixed in 3 levels (as shown above). Finally, the Parent, Child, and Grandchild are all of different types (e.g. we're not talking about 3 "humans", they don't have the same base).
However, it feels a bit odd to have the grandchild track the parent since that relationship can usually be derived from the parent-child relationship. Though, I cannot think of another way of doing it.
Is this model valid? Or is there a different way of doing it?
Best Answer
This answer is based upon your question as it stood before the clarification about each level being a different type. Since you have identified the need for different types, I agree with my answer as it originally appeared, and your self-answer documenting how you've approached this problem. Adding a single column to the grandchild table, referencing the top-most table, seems the simplest approach.
I'm leaving the below details in-place in case it helps a future visitor.
I would implement this with a cross reference table.
Below is a SQL Server-specific example; this table contains columns about the entities, including name, etc:
This table describes their relationships:
Each relationship must be unique, that is any given parent can only be related to any given child once.
Next, we create an
INSTEAD OF DELETE
trigger on theEntities
table that will handle deletes properly, by reparenting any relationships necessary, prior to removing the deleted Entity:Here we'll test that setup:
The results of the select above:
Here, we'll delete the "Parent" entity, and re-query the relationships:
The result:
Note that running
DELETE FROM dbo.Entities
(with noWHERE
clause) will delete all rows from both tables.To show a slight more complex example; imagine you have 2 grandparents, 2 parents, and single child:
If we remove
Parent 1
from theEntities
table:we see this:
This performs a cleanup of our test data: