I've got this situation where my region hierarchy is dependent not just on country but by the entity under which a sale is made. The table below is how I'd like the parenting to work. Region L3 entries then map on a 1:1 basis to Region L2 and again to L1. The entity specific region may roll up along with the geographical ones to roll up together.
|Entity||Country (Region L4)||Region L3|
|Entity 1||France||Entity specific Region|
|Entity 1||China||Entity specific Region|
At the moment I've got a mapping module doing the work for me. However, this means my Region L4 list is separate to my other 3 levels in terms of parent-child relationship. This solution also produces a significant amount of sparsity. If anyone has a trick, I'd love to hear it. Thanks in advance.