"MySQL troubleshooter" How to store a tree structure in a database (scenario three Closure Table)

Today, the third method of storing the tree structure in a database-the end table (forgive me for this blunt translation). ）。

Continue to use the chestnuts of the previous article, below is the structure diagram to be stored:

There are still a few questions to answer:

1. Check with your immediate boss for a small day.

2. Query the immediate staff under the management of Lao song.

3. Check all the bosses of the small day.

4. Check all the employees that Lao Wang manages.

Scenario three, Closure table method, to save each node and its various child nodes, that is, the record as the root node of all the child node information. Directly on the code is clear:

Here to create two tables, a table for storing information:

CREATE TABLE  INT varchar (+-varchar)

A table is used to store relationships:

CREATE TABLE  intint TINYINT (1 int) 

Here the root_id is used to store the path to its root node, node_id indicates that the eid,depth at the node represents the depth of the root node to that node, and Is_leaf indicates whether the node is a leaf node.

Next Insert the data:

As you can see, this relational table is a bit big, let's look at how the query works:

1. Check with your immediate boss for a small day.

You only need to find the root node ID in the relational table that node_id is a small day id,depth 1.

SELECT  from  WHERE e1.ename=' small day ' and rel.node_id=  and rel.depth=1 and E2.eid=rel.root_id

The query results are as follows:

　　

2. Query the immediate staff under the management of Lao song.

The idea is similar, as long as the query root_id for the old song Eid and depth of 1 node_id is its direct subordinate employee ID

SELECT  from  WHERE e2.ename=' Lao song ' and rel.root_id=  and rel.depth=1 and E1.eid=rel.node_id

The query results are as follows:

　　

3. Check all the bosses of the small day.

As long as the node_id is found in the relational table and the depth is greater than 0 root_id

SELECT  from  WHERE e1.ename=' small day ' and rel.node_id=  and rel.depth>0 and E2.eid=rel.root_id

The query results are as follows:

　　

4. Check all the employees that Lao Wang manages.

Just look in the relational table for root_id old Wang Eid,depth greater than 0 node_id can

SELECT  from  WHERE e2.ename=' Lao Wang ' and rel.root_id=  and rel.depth>0 and E1.eid=rel.node_id

The query results are as follows:

We can see that the complexity of the four queries is the same, which is the advantage of this storage method, and allows the other table to store only information that is closely related to the node, and looks more concise. But the shortcomings are also obvious, the relationship table will be very large, when the level is very deep, the structure is very large, the growth of relational table data will be faster, equivalent to the use of space efficiency in exchange for the time efficiency of the search.

At this point, the tree structure in the database stored in three ways is finished, the next comparison of three methods:

Scenario One: adjacency List

Advantages: Only the ancestor ID is stored, the storage data is small, and the structure is similar to the single linked list, which is convenient when querying the neighboring nodes. Adding a Delete node is simple.

Cons: When querying multi-level structures, it seems to be inadequate.

Application situation: It is applicable to the scene with little demand for multilevel query.

Scenario Two: Path Enumeration

Advantages: It is convenient to query multi-level structure. It is also ok to query adjacent nodes. It's easier to add or remove nodes.

Disadvantage: The path value that needs to be stored can be large, even exceeding the set maximum range, theoretically unable to expand indefinitely.

Application: The relatively simple structure of the scene is more suitable.

Scenario Three: Closure Table

Advantages: It is convenient to query the tree structure for any relationship.

Disadvantage: The amount of data to be stored is more, the index table needs more space, the increase and deletion of nodes is relatively troublesome.

Application occasions: The longitudinal structure is not very deep, the scene that the deletion operation is not frequent is more applicable.

Of course, you can also innovate their own other better storage solutions, if there is a better idea, welcome to communicate.

So far three kinds of programs are all finished, welcome to continue to pay attention to.

"MySQL troubleshooter" How to store a tree structure in a database (scenario three Closure Table)