I'm assuming this is not possible with PK and FK...
Let's say I have tables
Bare "parents", and
Ais the root
Bcan be the child of either
Ccan be a child of either
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
The issue I have is with the children
C. Since they could be a child of
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
B can have an FK 'id' to say that are that parent, 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
The general question is: how should I represent children that could have one parent OR another?
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: