Database Table Schema design with Tree Structure

How many child nodes does a node have? The left and right nodes of the node can be circled. The total number of child nodes (right-left-1) is 2. Fruit is used as an example, the total number of children and grandchildren is: (11-2-1) 24. At the same time, in order to display the tree structure more intuitively, we need to know the hierarchy of nodes in the tree.

How many child nodes does a node have? The left and right nodes of the node can be circled. The total number of child nodes is (right-left-1)/2. Fruit is used as an example, the total number of children and grandchildren is: (11-2-1)/2 = 4. At the same time, in order to display the tree structure more intuitively, we need to know the hierarchy of nodes in the tree.

How many child nodes does a node have? The left and right values of the node can be used to enclose the child nodes. The total number of child nodes is = (right-left-1)/2. Fruit is used as an example, the total number of children and grandchildren is: (11-2-1)/2 = 4. At the same time, in order to display the tree structure more intuitively, we need to know the hierarchy of the node in the tree, which can be achieved through the left-and right-value SQL query. Taking Fruit as an example: SELECTCOUNT (*) FROM Tree WHERE Lft <= 2 AND Rgt> = 11. To facilitate the description, we can create a view for the Tree and add a hierarchical series. The column value can be calculated using a custom function. The function is defined as follows:

CREATE FUNCTION dbo.CountLayer(    @node_id int)RETURNS intASbegindeclare @result intset @result = 0declare @lft intdeclare @rgt intif exists(select Node_id from Tree where Node_id = @node_id)beginselect @lft = Lft, @rgt = Rgt from Tree where node_id = @node_idselect @result = count(*) from Tree where Lft <= @lft and Rgt >= @rgtendreturn @resultendGO

Based on the hierarchical computing function, we create a view and add a series of new record node levels:

CREATE VIEW dbo.TreeViewASSELECT Node_id, Name, Lft, Rgt, dbo.CountLayer(Node_id) AS Layer FROM dbo.Tree ORDER BY LftGO

Create a stored procedure to calculate all child nodes and corresponding layers of a given node:

CREATE PROCEDURE [dbo].[GetChildrenNodeList](@node_id int)ASdeclare @lft intdeclare @rgt intif exists(select Node_id from Tree where node_id = @node_id)beginselect @lft = Lft, @rgt = Rgt from Tree where Node_id = @node_idselect * from TreeView where Lft between @lft and @rgt order by Lft ASCendGO

Now, we use the above stored procedure to compute all child nodes and corresponding layers of the node Fruit. The query results are as follows:

From the above implementation, we can see that the design scheme using Left and Right Value encoding is used. In the tree query time, only two database queries are required, eliminating recursion, in addition, the query conditions are numerical comparisons, and the query efficiency is extremely high. As the size of the tree continues to expand, the design scheme based on left-right value encoding will improve query efficiency more than the traditional recursive scheme. Of course, we have provided only a simple algorithm for getting the child node of a node. To use this tree, we need to implement functions such as inserting and deleting the same-layer translation nodes.

(2) obtain the genealogy path of a node

If we want to obtain the genealogy path of a node, we only need one SQL statement to complete the analysis based on the left and right values. Fruit is used as an example: SELECT * FROM Tree WHERE Lft <2 AND Rgt> 11 order by Lft ASC, a relatively complete stored procedure:

CREATE PROCEDURE [dbo].[GetParentNodePath](@node_id int)ASdeclare @lft intdeclare @rgt intif exists(select Node_id from Tree where Node_id = @node_id)beginselect @lft = Lft, @rgt = Rgt from Tree where Node_id = @node_idselect * from TreeView where Lft < @lft and Rgt > @rgt order by Lft ASCendGO

(3) Add a child node to a node

Suppose we want to add a new sub-node "Apple" under the node "Red", the tree will become as shown in, where the Red node is the new node.

Observe the changes in the left and right values of the nodes in the figure. I believe everyone should be able to infer how to write SQL scripts. We can provide a relatively complete Stored Procedure for inserting sub-nodes:

CREATE PROCEDURE [dbo].[AddSubNode](@node_id int,@node_name varchar(50))ASdeclare @rgt intif exists(select Node_id from Tree where Node_id = @node_id)beginSET XACT_ABORT ONBEGIN TRANSCTIONselect @rgt = Rgt from Tree where Node_id = @node_idupdate Tree set Rgt = Rgt + 2 where Rgt >= @rgtupdate Tree set Lft = Lft + 2 where Lft >= @rgtinsert into Tree(Name, Lft, Rgt) values(@node_name, @rgt, @rgt + 1)COMMIT TRANSACTIONSET XACT_ABORT OFFendGO

(4) Delete A node

If we want to delete a node, all child nodes of the node will be deleted at the same time, the number of deleted nodes is: (the right value of the deleted node-the left value of the deleted node + 1)/2, the left and right values of the remaining nodes are adjusted when they are greater than the left and right values of the deleted nodes. Let's see what happens to the tree. Take Beef as an example. The deletion effect is shown in.

Then we can construct the corresponding stored procedure:

CREATE PROCEDURE [dbo].[DelNode](@node_id int)ASdeclare @lft intdeclare @rgt intif exists(select Node_id from Tree where Node_id = @node_id)beginSET XACT_ABORT ONBEGIN TRANSCTIONselect @lft = Lft, @rgt = Rgt from Tree where Node_id = @node_iddelete from Tree where Lft >= @lft and Rgt <= @rgtupdate Tree set Lft = Lft – (@rgt - @lft + 1) where Lft > @lftupdate Tree set Rgt = Rgt – (@rgt - @lft + 1) where Rgt > @rgtCOMMIT TRANSACTIONSET XACT_ABORT OFFendGO

V. Summary

We can make a summary of the tree structure Schema design scheme that achieves unlimited grouping through left and right value encoding:

(1) Advantages: Infinite grouping is achieved without recursive operations, and the query condition is based on the comparison of integer numbers, which is highly efficient.

(2) Disadvantages: adding, deleting, and modifying nodes are costly and may involve changes to multiple aspects of data in the table.

Of course, this article only provides the implementation of several common CRUD algorithms. We can also add operations such as simultaneous node translation, node moving down, and node moving up. If you are interested, you can encode the code by yourself. I will not list it here. It is worth noting that implementing these algorithms may be troublesome and involve the sequential execution of many update statements. If the sequential scheduling is not well considered, if a Bug occurs, the entire tree structure table will be damaged. Therefore, you can use a temporary table as an intermediary for large-scale modification of the tree structure to reduce Code complexity. At the same time, we strongly recommend that you back up the table completely before making the modification, for emergency purposes. In the vast majority of database-based application systems dominated by queries, this solution is more suitable than the traditional database Schema built by parent-child inheritance relationships.

References: Storing Hierarchical Data in a Database Article