I'm assuming this is not possible with PK and FK...

Let's say I have tables A, B, and C.

  • A and B are "parents", and B and C are "children"
  • A is the root
  • B can be the child of either A or B
  • C can be a child of either A or B
  • B and C, the "children", can have one and only one "parent"

Eg. you could have this structure:

[A]━┳━[C] ┏━[C]
    ┗━[B]━╋━[C] ┏━[C]

Is there any design pattern where I could delete the root A and the delete could cascade to all the child C and B tables.

The issue I have is with the children B and C. Since they could be a child of A or B, I can't use a single FK, and adding a new FK for each possible parent is not scaleable.

I have tried creating a table P (parent) with a PK id, so that A and B can have an FK 'id' to say that are that parent, and B and C have an FK 'pid' to say that is their parent. The issue with this is that deleting the "root" A will not cascade to its children anymore, I'd have delete P.

The general question is: how should I represent children that could have one parent OR another?


Additional info

This database structure will be used as a client cache and on a server which is the authority. On the server, when A is deleted all the children shall be deleted and A shall be marked with a tombstone record. When the client synchronizes with the server, the server will read the tombstones and signal (in xml) the client to delete A which shall delete all children and grand-children of A. This shall be the same processing for all root records. Similarly, the signal to delete X shall delete X which will cascade to all its children. I want to avoid special processing where I'd have to actually delete P in order to delete A and all its children.

Useful related link:

  • 2
    "adding a new FK for each possible parent is not scaleable" How many tables do you expect to have that participate in this design? – ypercubeᵀᴹ Jun 29 '18 at 19:35
  • @ypercubeᵀᴹ Admittedly I do not expect the need for too many additional FKs, possibly one or two more. Something felt wrong about handling it that way though, but you make me question my aversion to it. Perhaps it will be an acceptable solution. – xtratic Jun 29 '18 at 19:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.