0

I need to insert tree structured data into my database. Lets say for now as an example that the tree has n levels and I have a Node table called T_Node with a column called 'name' (the amount of columns might grow later) and an Edge table called T_Edge.

In T_Node I have several nodes and in T_Edge I have just one relationship per node where from_id would be a child node and to_id would be the parent node of that child.

In order to get the tree underneath a given 'NodeName' I would use a recursive query like this one:

WITH cte as (   
    
    Select 1 xlevel, fromName.name child, toName.name parent
    from  dbo.T_Node fromName, dbo.T_Node toName, dbo.T_Edge r 
    WHERE MATCH(fromName-(r)->toName) 
    and fromName.name = 'NodeName'
    UNION ALL
    Select xlevel + 1, fromName.name, toName.name
    from cte c, dbo.T_Node fromName, dbo.T_Node toName, dbo.T_Edge r 
    WHERE MATCH(fromName-(r)->toName) and c.parent = fromName.name
    )

    SELECT *
    FROM cte
    ORDER BY xlevel, parent, child

My question is: is this the best approach for using and querying the graph tables efficiently to get the tree underneath? Should I improve it by creating more relationships in T_Edge or creating another table?

Laureano Bonilla
  • 335
  • 3
  • 18
  • You don't need graphs to represent trees, just use a `hierarchyid` column. You won't need a CTE at all. A `hierarchyyid` is essentially the path to a node, so finding all children is essentially a range operation on a B-Tree-indexed column. You can have multiple `hierarchyid` columns in a table, representing multiple hierarchies – Panagiotis Kanavos Sep 23 '20 at 15:55
  • You don't need graph capabilities to represent a tree in general. Graphs are for harder problems – Panagiotis Kanavos Sep 23 '20 at 15:57

0 Answers0