How to create hierarchical table in SQL?

Question

If there are three managers and their employees as shown in the photo, how to design that hierarchical in SQL.

Answer 1

I would just add a managerID field which is not required.

If the field is not set the person is one of the highest managers else he works as an employee for the person with the specific ID.

Answer 2

Here's how I'd do it:

CREATE TABLE dbo.Employee (
   EmployeeID INT NOT NULL IDENTITY,
     CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED (EmployeeID),
   Name nvarchar(50) NOT NULL,
   ManagerID INT NULL,
     CONSTRAINT [FK_Employee_Manager] FOREIGN KEY (ManagerId)
        REFERENCES dbo.Employee(EmployeeID),
   [Path] HierarchyID NULL
);

WITH e AS (
    SELECT * FROM (VALUES
        ('David'),
        ('Jill'),
        ('Tom'),
        ('Sara'),
        ('Mary'),
        ('John'),
        ('Wanida')
    ) AS x(Name)
)
INSERT INTO dbo.Employee
        ( Name )
SELECT Name
FROM e;

WITH h AS (
    SELECT * FROM (VALUES
        ('Jill', 'David'),
        ('Tom', 'Jill'),
        ('Sara', 'David'),
        ('Wanida', 'John')
    ) AS x(Employee, Manager)
)
UPDATE e
SET ManagerID = m.EmployeeID
FROM dbo.Employee AS e
JOIN h
    ON e.Name = h.Employee
JOIN dbo.Employee AS m
    ON h.Manager = m.Name;

A couple of notes:

• I assign an arbitrary ID to each employee. Referencing them by ID makes things like name changes easy without having to redo the validation.
• There's a self-referential foreign key to make sure that the manager is a valid employee. This prevents you from assigning an employee to a bogus manager and from deleting a manager that has subordinates
• Added a hierarchyid column to make certain kinds of queries easy (more on that later)

Now, let's populate that hierarchyid column!

WITH h AS (
    SELECT EmployeeID, ManagerID, CAST('/' + CAST(EmployeeID AS VARCHAR(10)) + '/' AS VARCHAR(MAX)) AS [Path] 
    FROM dbo.Employee
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmployeeID, e.ManagerID, h.[Path] + CAST(CAST(e.EmployeeID AS VARCHAR(10)) + '/' AS VARCHAR(MAX))
    FROM dbo.Employee AS e
    JOIN h
        ON e.ManagerID = h.EmployeeID
)
UPDATE e
SET [Path] = h.[Path]
FROM dbo.Employee AS e
JOIN h
    ON e.EmployeeID = h.EmployeeID

Here, we use a recursive CTE to calculate the path from any employee all they way up to the person that they ultimately report to in the structure. As for how to use the hierarchyid column…

SELECT e.*
FROM dbo.Employee AS e
JOIN dbo.Employee AS m
    ON e.[Path].IsDescendantOf(m.[Path]) = 1
WHERE m.Name = 'David'

This gets us all the people who report to David. You could also answer the question "who are the managers in Tom's reporting chain?" pretty easily as well. I'll leave that as an exercise to the reader. :)

You do pay a price in maintaining the Path column, but the benefit is not having to run that recursive query every time you want to calculate a chain. And, since you typically report on employees more often than you hire/fire/move someone to a new manager, I feel like the cost is justified.