Data structure for many to many hierarchies in SQL Server

Question

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 many to many relationships)

ItemHierarchy:

ParentCode ChildCode
--------------------
1            2
1            3
3            4
4            5
5            3
5            6

As you can see that 3 is child node for 1 and 3. I want to traverse records say for example that from the node 3.

I need to write a stored procedure and get all the ancestors of 3 and all the child nodes of 3.

Could you please let me know whether any possibilities to pull the data? If so, which data structure is OK for it.

Please note that my table is containing 1 million records and out of it 40% are having multiple hierarchies.

I did 'CTE' with level and incrementing it based upon the hierarchy but I'm getting max recursive error when we traverse from root to leaf level node. I have tried 'HierarchyID' but unable to get all the details when its having multiple parent for a node.

Update: I can set a recursion limit to max and run the query. Since it has millions of rows, I'm unable to get the output at all.

I want to create a data structure such that its capable to giving information from top to bottom or bottom to top (at any node level).

Could someone kindly please help me with that?

Answer 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, 8
FROM ItemHierarchy AS IH
WHERE IH.childCode = 5
UNION ALL
SELECT 8, 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:

DELETE FROM 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:

select distinct E1.NodeCode from EnumPath E1
inner join EnumPath E2
On E2.path like E1.path || '%'
where E2.NodeCode = 4 and E1.NodeCode != 4;

To find descendants of node #4:

select distinct E1.NodeCode from EnumPath E1
inner join EnumPath E2
On E1.path like E2.path || '%'
where E2.NodeCode = 4 and E1.NodeCode != 4;

Sqlfiddle demo