<div>
<div class="content wysiwyg-content">
Hi all,<br />
<br />
I need to store a rather complex hierarchy system in the database I'm building (SQL Server) and I'm struggling over whether to use Adjacency List or Nested Sets approach (i.e. Celko).<br />
<br />
Here is the company model I'm working with:<br />
<br />
- There is one parent agency with 3 subsidiary agencies (more subsidiary agencies may be added later)<br />
- One subsidiary agency has 7 hierarchy levels, one has 9 levels, and one has 5 levels.<br />
- New members are added frequently (at all levels of the hierarchy), but promotions are relatively infrequent.<br />
- There are two types of members: &quot;managers&quot; at the top few levels of the hierarchy that can recruit new members, and just plain members at the bottom levels of the hierarchy,<br />
- A member may belong to more than one subsidiary agency. So he could have more than one hierarchy that he reports to (and be at a different level for each).<br />
<br />
Does anyone have any feedback on a good way to model this, or the pros and cons of the approaches I'm considering? Any advice is appreciated!<br />
<br />

</div>
</div>


Hi all,

I need to store a rather complex hierarchy system in the database I'm building (SQL Server) and I'm struggling over whether to use Adjacency List or Nested Sets approach (i.e. Celko).

Here is the company model I'm working with:

- There is one parent agency with 3 subsidiary agencies (more subsidiary agencies may be added later)
- One subsidiary agency has 7 hierarchy levels, one has 9 levels, and one has 5 levels.
- New members are added frequently (at all levels of the hierarchy), but promotions are relatively infrequent.
- There are two types of members: "managers" at the top few levels of the hierarchy that can recruit new members, and just plain members at the bottom levels of the hierarchy,
- A member may belong to more than one subsidiary agency. So he could have more than one hierarchy that he reports to (and be at a different level for each).

Does anyone have any feedback on a good way to model this, or the pros and cons of the approaches I'm considering? Any advice is appreciated!



Adjacency List or Nested Sets?

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.