Database Table Design with left-right value encoding to store an infinitely Hierarchical Tree Structure

I have previously introduced a table Design Method for storing tree structure data by number of digits. For details, see:
Database Design Skills (I)

The advantage of this design scheme is that only one query statement can be used to obtain the first order traversal of a root node and all its child nodes. Because recursion is eliminated, a large amount of data records can greatly improve the list efficiency. However, this encoding scheme limits the maximum number of subnodes and the maximum number of layers allowed for each layer due to the number of information digits of the layer. At the same time, when adding a new node, you must first calculate whether the location of the new node exceeds the maximum limit.

The above design scheme must pre-set the maximum number of layers and the maximum number of sub-nodes of the category tree. It is not infinitely hierarchical and cannot be used in some scenarios. Is there a more perfect solution? Through Google's search, I found a brand new non-recursive query and infinitely hierarchical encoding solution-left and right values. The original program code is written in PHP. But by carefully reading its database table design instructions and related SQL statements, I have thoroughly understood this clever design concept, in this design, the need to delete nodes and pan at the same layer is added (the original article only provides the list and SQL statements for inserting subnodes ).

Below I try to describe this design scheme with a short text, a small number of charts, and related core SQL statements:

First, let's take a tree as an example:

Product
| --- Food
| --- Meat
| -- Pork
| --- Vegetables
| -- Cabbage
| --- Electrical Appliance
| -- TV
| -- Refrigerator

The data record for saving the tree using the left-right value encoding is as follows (set the table name to tree ):

Type_id

Name

LFT

Rgt

1

Product

1

18

2

Food

2

11

3

Meat

3

6

4

Pork

4

5

5

Vegetables

7

10

6

Cabbage

8

9

7

Electric Appliance

12

17

8

TV set

13

14

9

Refrigerator

15

16

The first time I saw the above data records, I believe most people do not know what rules the left value (LFT) and right value (rgt) are calculated based on, this table design does not seem to save the information of the parent node. The left and right values are combined with the tree. See:

1 commodity 18

+ --------------------------------------- +

2 Food 11 12 electrical appliances 17
+ ----------------- ++ ----------------------- +
3 meat 6 7 Vegetables 10 13 TV set 14 15 refrigerator 16
4 pork 5 8 cabbage 9

Please point your finger at the number in it. From 1 to 18, what will be discovered by those who have learned the data structure? Yes, the order in which your fingers move is the order in which the tree is first traversed. Next, let me show you how to use the left and right values of a node to obtain the parent node, the number of child nodes, and the number of layers in the tree.

Assume that we want to traverse the list of node "food" and its children and grandchildren in sequence. We only need to use the following SQL statement:

Select * from tree where LFT between 2 and 11 order by LFT ASC

The query result is as follows:

Type_id

Name

LFT

Rgt

2

Food

2

11

3

Meat

3

6

4

Pork

4

5

5

Vegetables

7

10

6

Cabbage

8

9

How many child nodes does a node have? Very simple, descendant Total = (right-left-1)/2

Taking the node "food" as an example, the total number of children and grandchildren = (11-2-1)/2 = 4

At the same time, when we display the entire category tree in the list, in order to help users intuitively see the hierarchy of the tree, the corresponding indentation is generally performed based on the number of layers of the node, how to calculate the number of layers of nodes in the tree? You only need to query the left and right values. Take the node "food" as an example. The SQL statement is as follows:

Select count (*) from tree where LFT <= 2 and rgt> = 11

To facilitate the list, we can create a view for the tree table and add a layer column. The layers of this type can be computed by writing a custom function. This function is as follows:

Create Function DBO. countlayer
(
@ Type_id int
)
Returns int
As
Begin
Declare @ result int
Set @ result = 0
Declare @ LFT int
Declare @ rgt int
If exists (select 1 from tree where type_id = @ type_id)
Begin
Select @ LFT = LFT, @ rgt = rgt from tree where type_id = @ type_id
Select @ result = count (*) from tree where LFT <= @ LFT and rgt> = @ rgt
End
Return @ result
End
Go

Then, we create the following view:

Create view DBO. Treeview
As
Select type_id, name, LFT, rgt, DBO. countlayer (type_id) as layer from DBO. Tree order by LFT
Go

A stored procedure that first traverses a given node and its Child Nodes in sequence:

Create procedure [DBO]. [gettreelistbynode]
(
@ Type_id int -- ID of a given Node
)
As
Declare @ LFT int
Declare @ rgt int
If exists (select 1 from tree where type_id = @ type_id)
Begin
Select @ LFT = LFT, @ rgt = rgt from tree where type_id = @ type_id
Select * From Treeview where LFT between @ LFT and @ rgt order by LFT ASC
End
Go

Now, we use the stored procedure above to list the node "food" and all its children and grandchildren. The query results are as follows:

Type_id

Name

LFT

Rgt

Layer

2

Food

2

11

2

3

Meat

3

6

3

4

Pork

4

5

4

5

Vegetables

7

10

3

6

Cabbage

8

9

4

The design scheme of left-right value encoding is adopted. Because only two queries are required during the class tree's time span, recursion is eliminated, and the query conditions are all numbers, which is highly efficient, the more record entries in the category tree, the higher the execution efficiency. I believe many people are excited about this design scheme. Let's take a look at how to insert, delete, and shift nodes at the same layer in this table structure (change the order of nodes at the same layer).

Suppose we want to add a subnode "beef" under the node "meat", the tree will become:

1 product 18 + 2

+ -------------------------------------------- +

2 Food 11 + 2 12 + 2 Electrical Appliances 17 + 2
+ ----------------- ++ --------------------------- +
3 meat 6 + 2 7 + 2 Vegetables 10 + 2 13 + 2 TV set 14 + 2 15 + 2 refrigerator 16 + 2

+ ------------- +

4 pork 5 6 beef 7 8 + 2 cabbage 9 + 2

After reading the changes in the left and right values of the corresponding node, I believe everyone knows how to write the corresponding SQL script? The following describes the complete procedure for inserting sub-nodes:

Create procedure [DBO]. [addsubnodebynode]
(
@ Type_id int,
@ Name varchar (50)
)
As
Declare @ rgt int
If exists (select 1 from tree where type_id = @ type_id)
Begin
Set xact_abort on
Begin transaction
Select @ rgt = rgt from tree where type_id = @ type_id
Update tree set rgt = rgt + 2 where rgt >=@ rgt
Update tree set LFT = LFT + 2 where LFT >=@ rgt
Insert into tree (name, LFT, rgt) values (@ name, @ rgt, @ rgt + 1)
Commit transaction
Set xact_abort off
End
Go

Then, let's Delete the "TV" node and see what will happen to the tree:

1 goods 20-2

+ ----------------------------------- +

2 Food 13 14 Electric Appliances 19-2
+ ----------------- +
3 Meat 8 9 vegetables 12 17-2 refrigerator 18-2
+ ---------- +

4 pork 5 6 beef 7 10 cabbage 11

The stored procedure is as follows:

Create procedure [DBO]. [delnode]
@ Type_id int
As
Declare @ LFT int
Declare @ rgt int
If exists (select 1 from tree where type_id = @ type_id)
Begin
Set xact_abort on
Begin transaction
Select @ LFT = LFT, @ rgt = rgt from tree where type_id = @ type_id
Delete from tree where LFT >=@ LFT and rgt <= @ rgt
Update tree set LFT = LFT-(@ rgt-@ LFT + 1) Where LFT> @ LFT
Update tree set rgt = rgt-(@ rgt-@ LFT + 1) Where rgt> @ rgt
Commit transaction
Set xact_abort off
End

Note: deleting a node deletes all child nodes of the node 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, each node has a unique left value and a unique right value at the same time. Therefore, after a node is deleted, the left and right values of the corresponding nodes must be adjusted: decrease (the right value of the deleted vertex-the left value of the deleted vertex + 1 ).

Finally, let's take a look at what the tree will become after the translation node "electric appliance" moves it and all its children and grandchildren to the node "food:

1 commodity 18

+ ----------------------------------- +

14-12 electrical appliances 17-12 2 + 4 food 13 + 4
+ ---------------------- +
15-12 refrigerator 16-12 3 + 4 Meat 8 + 4 9 + 4 vegetables 12 + 4
+ ------------------- +
4 + 4 pork 5 + 4 6 + 4 beef 7 + 4 10 + 4 cabbage 11 + 4

After the switch, you can observe the changes in the left and right values of the two nodes in the same layer and all their children and grandchildren. We can find an obvious rule, the left and right values of the node "electric appliance" and all its children and grandchildren are reduced by 12, while the left and right values of the node "food" and all its children and grandchildren are increased by 4. The number of node "electric appliance" + its descendant nodes is 2, and the number of node "food" + its descendant nodes is 6. Is there any relationship between them? Do you still remember the comment after the stored procedure of deleting a node? Each node has a unique left value and a unique right value. Let's set the number of nodes to 2, which is equal to the magnitude to be adjusted between the left and right values of the nodes. Based on this rule, we can write a stored procedure similar to the following to implement the same-layer forward node function:

Create procedure [DBO]. [movenodeup]
@ Type_id int
As
Declare @ LFT int
Declare @ rgt int
Declare @ layer int
If exists (select 1 from tree where type_id = @ type_id)
Begin
Set xact_abort on
Begin transaction
Select @ LFT = LFT, @ rgt = rgt, @ layer = layer from Treeview where type_id = @ type_id
If exists (select * From Treeview where rgt = @ lft-1 and layer = @ layer)
Begin
Declare @ brother_lft int
Declare @ brother_rgt int
Select @ brother_lft = LFT, @ brother_rgt = rgt from Treeview where rgt = @ lft-1 and layer = @ Layer
Update tree set LFT = LFT-(@ brother_rgt-@ brother_lft + 1) Where LFT >=@ LFT and rgt <= @ rgt
Update tree set LFT = LFT + (@ rgt-@ LFT + 1) Where LFT >=@ brother_lft and rgt <= @ brother_rgt
Update tree set rgt = rgt-(@ brother_rgt-@ brother_lft + 1) Where rgt> @ brother_rgt and rgt <= @ rgt
Update tree set rgt = rgt + (@ rgt-@ LFT + 1) Where LFT >=@ brother_lft + (@ rgt-@ LFT + 1) and rgt <= @ brother_rgt
End
Commit transaction
Set xact_abort off
End

Note: You can use temporary tables for simultaneous node translation to reduce Code complexity. You do not need to use temporary tables for processing, but the update statement sequence must be carefully considered. Otherwise, if a bug occurs, the damage to the entire category table is astonishing. We strongly recommend that you back up the category table completely before doing the above work.

The stored procedures for the same layer move down are similar to those for the same layer move up. If you are interested, you can write and understand the details. I will not list them here.

Finally, I will summarize the above scheme to implement an infinite classification category tree based on the left and right value encoding:

Advantage: it achieves unlimited classification without recursion, and the query condition is Based on Integer comparison, which is highly efficient. You can perform regular operations such as sequence tables, adding, modifying, deleting, and moving at the same layer to meet your requirements.

Disadvantage: because the left-right value encoding method is different from the common Arabic numerals in intuitive sorting, and the node hierarchy in the tree, the order is not displayed intuitively, it must be obtained after a simple formula, and it takes some time to thoroughly understand its mathematical model. In addition, this solution is used to write related stored procedures, such as add and delete. At the same layer, the translation node needs to query and modify the entire tree, resulting in high Code complexity and coupling, modification and maintenance are highly risky.