'Can I do a DAG with SQL Server HierarchyId?

I'm developing an organizational chart in SQL Server 2016. I have some employees report to multiple managers which make the data structure a Directed Acyclic Graph.

How can I design the employee table with employee to have multiple managers are parents by using the SQL datatype HierarchyId?

sqlsql-serverhierarchical-datahierarchyid


Solution 1:[1]

You could use this structure to store a record below each manager which represents the employee and points to the actual employee using the SourceNode column.

CREATE TABLE [dbo].[Employees] (
    [EmployeeNode] [hierarchyid] NOT NULL,
    [PrincipalName] [nvarchar](50) NOT NULL,
    [SourceNode] [hierarchyid] NULL
)

Example:

PrincipalName    EmployeeNode    SourceNode
-------------    ------------    ----------
Employees        /               null
  Tom Smith      /1/             null
  Ryan Sackett   /2/             null
    *Tom Smith   /2/1/           /1/
  Alita Ford     /3/             null
  Jean Thomas    /4/             null
    *Tom Smith   /4/1/           /1/

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Paul Van Gundy

Related Questions