Can I do a DAG with SQL Server HierarchyId?

Question

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?

One table with employees and their data (names, b-days etc). Another table with parent-child relations. Unique key on parent and child ids. — gofr1, Jul 18 '16 at 12:55
No, you cannot. The hierarchies that it's designed to model have a single parent relationship. — Damien_The_Unbeliever, Jul 20 '16 at 09:19
Just to confirm another example, can I create a graph as in this example http://stackoverflow.com/questions/6397641/converting-directed-acyclic-graph-dag-to-tree/6486793 with **hierarchyid**? — wonderful world, Jul 20 '16 at 09:24

score 0 · Answer 1 · answered May 01 '22 at 01:22

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/

Can I do a DAG with SQL Server HierarchyId?

1 Answers1