An ideal method for storing tree structure data in relational database

an ideal method for storing tree structure data in relational database

In a variety of relational database-based application development, we often need to store the tree structure of data, there are many popular methods, such as the adjacency list model (the adjacency list models), on this basis there are many people for different needs to make corresponding improvements, But there are always a variety of flaws in some ways.
So what are the characteristics of the ideal tree structure? Data storage redundancy is small, intuitive, and easy to return the entire tree structure data; it is easy to return a subtree (for easy layering); fast access to a node's ancestor spectrum path, insert, delete, move node high efficiency and so on. With these demands. I have found a lot of data, discovered a kind of ideal tree-type structure storage and operation algorithm, improved the pre-sequence traversal tree model (the Nested Set model).
First, the data

In this article, give an example of an online food store tree diagram. The grocery store organizes food by category, color and variety. The tree diagram is as follows:

Second, adjacency list model (the adjacency)

In this model, the table structure data for the above data in the relational database is usually as follows:

Because the model is relatively simple, the algorithm is not described in detail here, and some of its shortcomings are listed below:

In most programming languages, he runs very slowly and with poor efficiency. This is mainly caused by "recursion". Every time we query a node, we have access to the database. Each database query takes some time, which makes it very slow for the function to handle a large tree. The second reason why this function is not too fast may be the language you are using. Unlike the language of Lisp, most languages are not designed for recursive functions. The second reason that the function is not too fast for each node may be the language you are using. Unlike the language of Lisp, most languages are not designed for recursive functions. For each node, the function calls himself, creating a new instance. Thus, for a 4-tier tree, you might run 4 copies of the function at the same time. It takes a chunk of memory for each function and takes a certain amount of time to initialize, so recursion is slow when dealing with a tree.

Iii. improved pre-sequence traversal tree model (the Nested Set models)

Principle:

Let's lay out the trees in a horizontal way first. Start with the root node ("food"), and then his left is written on 1. Then write 2 to the left of "Fruit" in the Order of the tree (top to bottom). So you walk along the edge of the tree (this is "traversal"), and then write numbers on the left and right sides of each node. Finally, we go back to the root node "food" on the right to write 18. The following is a tree labeled with numbers, and the order of traversal is marked with arrows.

============================= the following figure belongs to the later =================================

=============================================================================

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 are on time for the relationship between each node. Because "Red" has 3 and 62 values, it is followed by a "food" node that has a value of 1-18. Similarly, we can infer that all nodes with an lvalue greater than 2 and a right value of less than 11 are all followed by a 2-11 "Fruit" node. In this way, the structure of the tree is stored by the left and right values. This method of counting the entire tree node is called "improved pre-sequence traversal tree" algorithm.

Table structure Design:

Common operations:

The following is a list of some common operations SQL statements

Returns the complete tree (retrieving a full trees)
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
Returns the subtree of a node (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 ancestor spectrum path of a node (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 the subtree (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
Return all leaf nodes (finding all the leaf Nodes)
SELECT name from nested_category WHERE RGT = lft + 1
Insert 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 consoles ', @myRight + 1, @myRight + 2);
UNLOCK TABLES;
Delete node (Deleting Nodes)
LOCK TABLE nested_category WRITE;
SELECT @myLeft: = LfT, @myRight: = RGT, @myWidth: = rgt-lft + 1
From Nested_category
WHERE name = ' GAME consoles ';
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 database