I have to develop a database solution that will allow a query to return a single, flat resultset based on data contained in a heirarchical structure. Let me explain...
All data is maintained using a container approach based on the company's organizational structure. At the core of this is the OrganizationalUnits table with the following schema:
UniqueID uniqueidentifier (PK)
Name nvarchar(50)
ParentOU uniqueidentifier (FK)
Each user is associated with an OU but they can be assigned to any level in the heirarchy. For example, a Division Manager would be associated with the OU for the division whereas an Design Engineer would belong to the applicable department underneath the division.
When a new record is created in the database, let's use the Customers table for our discussion, the record is marked with the OU of the user that created it. So, the Customers table schema is similar to:
UniqueID uniqueidentifier (PK)
OU uniqueidentifier (FK)
Name nvarchar(50)
:
:
The heirarchy that we have is currently 4 levels deep (but may be changing as the company restructures this year): Company->Division->Group->
Department
Anyway, the query needs to return all records from the Customers table corresponding to the current user's organizational unit AND all child OUs. In other words, if the current user belongs to a department level OU, the query is nice and straight forward. But, if the user is at the divisional level, the query needs to return records from that OU as well as all of the child groups and their child departments.
Unfortunately, because the depth of the heirarchy is variable today and in the future, I don't know where to begin. I know that this is a common data structure today, so I'm hoping someone has crossed this bridge before and can point me in the right direction. Thanks in advance.
Start Free Trial
