An ideal method for storing tree structure data in relational databases-just do it-blog

Document directory

• I. Data
• II. The adjacency list Model)
• 3. Improved forward traversal tree model (the nested set model)

 

An ideal method for storing tree structure data in relational databases

In the development of various application systems based on relational databases, we often need to store data in a tree structure. There are many popular methods, such as the adjacency list model ), on this basis, many people have made corresponding improvements to different needs, but there are always various defects in some aspects.
What are the characteristics of the ideal tree structure? Small Data Storage redundancy and strong intuition; easy to return the entire tree structure data; can easily return a child tree (convenient for hierarchical loading ); quickly obtain the original spectrum path of a node; insert, delete, and move nodes with high efficiency. With these requirements, I found a lot of information and an ideal tree-structure data storage and operation algorithm, and improved the nested set model ).

I. Data

In this article, we will give an example of an online grocery store tree. This Food Store organizes food by category, color, and variety. The tree structure is as follows:

II. The adjacency list Model)

In this model, the table structure data of the above data in the relational database is usually shown in:

Because the model is relatively simple, we will not detail its algorithm here. The following lists some of its shortcomings:

In most programming languages, it runs very slowly and is very inefficient. This is mainly caused by recursion. Each time we query a node, We need to access the database. Each database query takes some time, which slows down the processing of huge trees. The second reason that this function is not too fast may be your language. Unlike lisp, most languages are not designed for recursive functions. This function is not too fast for each node. The second reason may be your language. Unlike lisp, most languages are not designed for recursive functions. For each node, the function calls itself to generate a new instance. In this way, for a layer-4 tree, you may need to run four function copies at the same time. Every function occupies a piece of memory and requires some time for initialization. In this way, recursion is slow when processing big trees.

3. Improved forward traversal tree model (the nested set model)

Principle:

First, we should open the tree horizontally. Start from the root node ("food") and write 1 on the left. Then write 2 to the left of "Fruit" in the tree Order (from top to bottom. In this way, you can go along the boundary of the tree (this is "traversal") and write numbers on the left and right sides of each node at the same time. Finally, we return to the root node "food" and write 18 on the right. Below is a tree labeled with numbers, and the order of traversal is marked with arrows.

We call these numbers the left and right values (for example, the left Value of "food" is 1 and the right value is 18 ). As you can see, these numbers reflect the relationship between each node on time. Because "red" has 3 and 6 values, it is followed by a "food" node with 1-18 values. Similarly, we can infer that all nodes with a left value greater than 2 and a right value less than 11 will be followed by "Fruit" nodes with 2 to 11. In this way, the tree structure is stored through the left and right values. This method for calculating nodes in the entire tree is called the "improved forward traversal Tree" algorithm.

Table Structure Design:

Common Operations:

The SQL statements for some common operations are listed below

Returns the complete tree (retrieving a full tree)

Select node. Name
From nested_category node, nested_category parent
Where node. LFT between parent. LFT and parent. rgt
And parent. Name = 'electronics'
Order by node. LFT

Find the immediate subordinates of a node)

Select v .*
From (select node. Name,
(Count (parent. Name)-(AVG (sub_tree.depth) + 1) Depth
From nested_category node,
Nested_category parent,
Nested_category sub_parent,
(Select v .*
From (select node. Name, (count (parent. Name)-1) Depth
From nested_category node, nested_category parent
Where node. LFT between parent. LFT and parent. rgt
And node. Name = 'portable electronics'
Group by node. Name) V,
Nested_category t
Where v. Name = T. Name
Order by T. LFT) sub_tree
Where node. LFT between parent. LFT and parent. rgt
And node. LFT between sub_parent.lft and sub_parent.rgt
And sub_parent.name = sub_tree.name
Group by node. Name) V,
Nested_category t
Where v. Name = T. Name
And v. depth <= 1
And v. Depth> 0
Order by T. LFT

Returns the path of a node's ancestor spectrum (retrieving a single path)

Select parent. Name
From nested_category node, nested_category parent
Where node. LFT between parent. LFT and parent. rgt
And node. Name = 'flash'
Order by node. LFT

Returns the depth of all nodes (finding the depth of the nodes)

Select v .*
From (select node. Name, (count (parent. Name)-1) Depth
From nested_category node, nested_category parent
Where node. LFT between parent. LFT and parent. rgt
Group by node. Name) V,
Nested_category t
Where v. Name = T. Name
Order by T. LFT

Returns the depth of a sub-tree)

Select v .*
From (select node. Name,
(Count (parent. Name)-(AVG (sub_tree.depth) + 1) Depth
From nested_category node,
Nested_category parent,
Nested_category sub_parent,
(Select v .*
From (select node. Name, (count (parent. Name)-1) Depth
From nested_category node, nested_category parent
Where node. LFT between parent. LFT and parent. rgt
And node. Name = 'portable electronics'
Group by node. Name) V,
Nested_category t
Where v. Name = T. Name
Order by T. LFT) sub_tree
Where node. LFT between parent. LFT and parent. rgt
And node. LFT between sub_parent.lft and sub_parent.rgt
And sub_parent.name = sub_tree.name
Group by node. Name) V,
Nested_category t
Where v. Name = T. Name
Order by T. LFT

Returns all the leaf nodes (finding all the leaf nodes)

Select name from nested_category where rgt = LFT + 1

Insert a node (adding new nodes)

Lock table nested_category write;
Select @ myright: = rgt from nested_category where name = 'televisions ';
Update nested_category set rgt = rgt + 2 where rgt> @ myright;
Update nested_category set LFT = LFT + 2 where LFT> @ myright;
Insert into nested_category
(Name, LFT, rgt)
Values
('Game rules', @ myright + 1, @ myright + 2 );
Unlock tables;

Ing nodes)

Lock table nested_category write;
Select @ myleft: = LFT, @ myright: = rgt, @ mywidth: = rgt-LFT + 1
From nested_category
Where name = 'game rules ';
Delete from nested_category where LFT between @ myleft and @ myright;
Update nested_category set rgt = rgt-@ mywidth where rgt> @ myright;
Update nested_category set LFT = LFT-@ mywidth where LFT> @ myright;
Unlock tables;

An ideal method for storing tree structure data in relational databases-just do it-blog