I wouldn't think about Adjacency Lists and Nested Sets as mutually exclusive. I tend to think about nested sets as an extention of an adjacency list, were you do a little more bookkeeping work to maintain your lft and rgt indexes but you get the added benefit of being able to easily select all of the children of specific node with a simple select statement.
As the previous poster suggests, you can model your Departments table as an Adjacency list (or extend that to a nested sets approach) and the Employees table is trivial, then your table of Employee-Deptartment would just list the departments that any employee may belong to.
The beauty of the nested sets approach for the Department is this: Say you have a manager that is at level 2 (of the 9 level department subsidiary for example) and you want to find out all the employees that he manages directly or indirectly. With the Adjacency list approach, you'd have to do a bunch of querys to pull out all the children of the manager at level 3, then level 4.... etc, until you get no more records. However, the nested sets approach would allow you to write a single select statement that looks like this:
SELECT *
FROM Departments P INNER JOIN Departments C ON C.lft>=P.lft AND C.lft<=P.rgt
INNER JOIN tblEmptDept ED ON C.DeptID=ED.DeptID
INNER JOIN tblEmployee E ON ED.EmpID=E.EmpID
WHERE P.Dept=[Manager's Department]
I've used the nested sets approach many times, and once you get used to it, the queries you can write are fantastic.
Hope this helps,
Main Topics
Browse All Topics
by: dotmeatPosted on 2005-08-06 at 15:36:51ID: 14615728
I sugesst that you have a tblDepartment like:
DeptID
Level
ParentID
And tblEmp (employee) as:
EmpID
EmptName
EmptPhone,
...
And tblEmptDept (employee-Department, because 1 emp can belong 2 departments) as:
EmptID
DeptID.
----------
You can consider "who are managers and plain members" by the level field.
You would have 3 levels in the hierarchy, so it is easy to get the whole hierarchy by determing the Level of 0, 1, 2 and by ParentID field.