Use triggers and T-SQL to create a functional and self-maintaining solution for managing hierarchical data
A hot Internet startup lures Andrew away to a new job, so Steven and Michael—who reported to Andrew—need a new manager. Superstar Janet takes Steven and Michael under her management wing, even though she already directly manages Robert, Laura, and Ann. Robert, in turn, manages his own team of employees. But recently, Robert has been looking for better hours and more pay. If Robert leaves, who'll oversee David, Ron, and Dan—not to mention David's assistant, James? And, more important, how will IT keep track of all the employee-manager changes in the company's employment hierarchy?
Hierarchical structures, also called trees, have hierarchical dependencies between their members. A common hierarchical structure is an organizational chart that describes the relationships between a company's employees. A manager is in charge of certain employees, those employees might be in charge of other employees, and so on.
The SQL language doesn't have built-in support for such hierarchical structures—nor does SQL Server. So how do you handle hierarchies with relational database management systems (RDBMSs) such as SQL Server? Consider Figure 1, page 56, which shows a simple organizational chart. Notice that each employee has a manager except Nancy, who is the top-level manager. The most common way to represent such a structure in a relational database is to use pairs of columns: One column holds the employees' (the children's) IDs; the other holds the IDs of their managers (the parents). The problem with this solution is that Nancy doesn't have a manager but you still need to store a value in her manager ID column. A common way to handle this problem is to store a NULL in the manager ID column. Another solution would be to store Nancy's employee ID in the manager ID column, making Nancy her own manager.
To see how you can maintain hierarchies with SQL Server, let's create a simple table that holds information about the employees in Figure 1's organizational chart. You can then use triggers, T-SQL queries, and stored procedures to track employee ID, employee name, manager ID, and employee and manager salaries as employees join the company, change jobs within the company, and leave the company. A script that creates the Employees table and populates it with data is available online at http://www.sqlmag.com. For our example, let's use NULL as Nancy's manager ID value.
Querying the Employees Table
Without adding any information to the Employees table, you can use T-SQL statements to answer certain questions about the hierarchy of employees. For example, to find out who is the most senior manager in the organization, you can run the query
SELECT * FROM Employees
WHERE mgrid IS NULL
To find the names of all employees and their managers, you can run
SELECT E.empname AS EmployeeName, M.empname AS ManagerName
FROM Employees AS E LEFT OUTER JOIN Employees AS M
ON E.mgrid = M.empid
This query uses an outer join because an inner join would have excluded Nancy, the senior manager, from the report. The left outer join includes all the rows from the left table (the Employees table), whether or not those rows have matches in the right table (which represents managers).
The following query shows you Robert's immediate subordinates:
SELECT * FROM Employees
WHERE mgrid = 7
And to see all leaf-level employees—employees with no subordinates—you can run
SELECT * FROM Employees AS M
WHERE NOT EXISTS (SELECT empid
FROM Employees AS E
WHERE E.mgrid = M.empid)
This query is a correlated subquery that returns only employees who aren't managers.
Maintaining New Data
Although you can answer some questions by using the existing Employees table, you can't easily meet all of your needs for employee and manager information. Consider the following informational requests:
- Show me all employees so that I can see their hierarchical dependencies.
- Show me details about Robert and all his subordinates at all levels.
- What is the total salary of Robert and all his subordinates at all levels?
- Show me details about all leaf-level employees who report to Janet.
- Show me details about all employees who are two levels under Janet.
- Show me the chain of management leading to James.