Data Structure For Many To Many Hierarchies In Sql Server

I have the following data structure already in the system. ItemDetails: ID Name -------- 1 XXX 2 YYY 3 ZZZ 4 TTT 5 UUU 6 WWW And the hierarchies are in separate table (with

Solution 1:

Using RDBMS for hierarchical data structure is not recommended, its why graph database have been created. BTW following Closure Table pattern will help you. The Closure Table solution is a simple and elegant way of storing hierarchies. It involves storing all paths through the tree, not just those with a direct parent-child relationship.

The key point to use the pattern is how you must fill ItemHierarchy table. Store one row in this table for each pair of nodes in the tree that shares an ancestor/descendant relationship, even if they are separated by multiple levels in the tree. Also add a row for each node to reference itself. Think we have a simple graph like bellow: The doted arrows shows the rows in ItemHierarchy table: To retrieve descendants of #3:

SELECT c.*
FROM ItemDetails AS ID
JOIN ItemHierarchy AS IH ON ID.ID = IH.ChildCode
WHERE IH.ParentCode = 3;

To retrieve ancestors of #3:

SELECT c.*
FROM ItemDetails AS ID
JOIN ItemHierarchy AS IH ON ID.ID = IH.ParentCode 
WHERE IH.ChildCode = 3;

To insert a new leaf node, for instance a new child of #5, first insert the self-referencing row. Then add a copy of the set of rows in TreePaths that reference comment #5 as a descendant (including the row in which #5 references itself), replacing the descendant with the number of the new item: INSERT INTO ItemHierarchy (parentCode, childCode)

SELECT IH.parentCode, 8FROM ItemHierarchy AS IH
WHERE IH.childCode =5UNIONALLSELECT8, 8;

To delete a complete sub-tree, for instance #4 and its descendants, delete all rows in ItemHierarchy that reference #4 as a descendant, as well as all rows that reference any of #4’s descendants as descendants:

DELETEFROM ItemHierarchy 
WHERE chidCode IN (SELECT childCode
FROM ItemHierarchy 
WHERE parrentCode =4);

UPDATE Since the sample data you have shown us leads to recursive loops(not hierarchies) like:

1->3->4->5->3->4->5

Following Path Enumeration pattern will help you. A UNIX path like /usr/local/lib/ is a path enumeration of the file system, where usr is the parent of local, which in turn is the parent of lib. You can create a Table or View from ItemHierarchy table, calling it EnumPath: Table EnumPath(NodeCode, Path) For the sample data we will have: To find ancestors of node #4:

selectdistinct E1.NodeCode from EnumPath E1
innerjoin EnumPath E2
On E2.path like E1.path ||'%'where E2.NodeCode =4and E1.NodeCode !=4;

To find descendants of node #4:

selectdistinct E1.NodeCode from EnumPath E1
innerjoin EnumPath E2
On E1.path like E2.path ||'%'where E2.NodeCode =4and E1.NodeCode !=4;

Sqlfiddle demo