Need help with Bridge Table Design for: 1) Ragged-Hierarchy 2) Parent Nodes can be used more than once.

Hi,

I need some advice designing a dimensional model with a bridge table to cater for a hierarchy:

1) Ragged - Hierarchy (depth unknown):


2) Parent Nodes - as well as Leaf Nodes could be repeated at different levels:

Having children with multiple parents is not a difficult issue, a bill of materials hierarchy is like that. But having a parent with different children isn't allowed.

In your example, (3)C is two different things: one has children 8,5 and the other 5,6. They need to be treated as different parents with their own identity (key). If the hierarchy key is a discriminator, it needs to be part of the keys shown in example 2.