Have you extended nested sets for hierarchical data modeling involving multiple parent nodes for a child node? What are your experiences?

Question

I am looking to use this concept in one of my upcoming project.

More info: Managing Hierarchical Data in MySQL.

Please share your experiences good or bad with examples.

I am adding more information to make it more broad:

I have child items that can have more than one parent (example: a user can belong to city and also a group called UserDefinedRegion), which the typical hierarchical models do not support, whether it is adjacency list or nested sets.

I am pasting the use case here for clarity:

---

Background: Currently the system has a fixed hierarchy in place which is State->County->City->User

1. Sales Manager logs in to the system and creates a new group which can by at the same level as City, or County.

2. Sales Manager logs in to the system and creates a new group which can be in between State and county or County and City.

3. Once the sales manager creates the groups, he should be able to view all the necessary reports rolled up the next day in his dashboard.

---

As you can see, second point can easily be accomplished by nested sets, but not the first point, which will introduce new parents nodes for the same child node.

So far the following solutions were proposed by stackOverflow users:

1. Network Node structure supported by Network Database.
2. Directed Acyclic graphs.

I am definitely looking for a RDBMS solution. It looks like not many have encountered multiple parent nodes in heirarchical data models in real life.

Answer 1

Your requirement for multiple parents immediately violates the fundamental nature of nested sets, as pointed out in your referenced article, so I'd say you're headed for trouble to start with. Since you'll be using a relational database, which (using it's core capabilties) will handle everything you've described so far, I think just working in that conceptual framework and polishing your skills will provide everything you need, without adding additional abstractions that (at least in this case) don't add any value.

If you still want to go there, I'd call this a networked node structure. Here's a reference.

Answer 2

As you will probably be using stored procedures for some operations, make sure that they really perform well enough for your needs! This could be a problem if you use MySQL, in my experience.

Regarding the new requirement (multiple parents): You are now into much more problematic stuff when using a RDBMS, depending on what kind of queries you need to run against the data. I compared the RDBMS approach to using a graph database on this wiki page. If you are only interested in the RDBMS approach, take a look at A Model to Represent Directed Acyclic Graphs (DAG) on SQL Databases.