2

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]
          ┗━[B]━┻━[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?

Thanks.

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:

xtratic
  • 121
  • 4

0 Answers0