database table Design of tree structure __ Database

Design of database table with tree-shaped structure

In the process of programming, we often use the tree structure to characterize the relationship of some data, such as Enterprise's subordinate departments, column structure, commodity classification and so on, usually, these tree-like structures need to use the database to complete the persistence. However, all kinds of relational databases are stored in the form of two-dimensional tables, so it is not possible to store the tree in the DBMS directly, and the design of the appropriate schema and its corresponding CRUD algorithm is the key to realize the structure of the relational database.

The ideal tree structure should have the following characteristics: The data storage redundancy is small, intuitive, the retrieval traversal process is simple and efficient, node additions and deletions to check crud operation efficiency. Inadvertently search on the internet to a very clever design, the original is English, after looking at the feeling a little meaning, so they sorted out. This article will introduce two kinds of tree structure schema design scheme: One is intuitive and simple design idea, the other is based on the left-right value coding improvement scheme.

I. Basic data

This article cites an example of a food family tree to explain how foods are organized by category, color, and variety, as follows:

Schema design driven by inheritance relation

The most intuitive analysis of tree structure is the relationship between nodes, by showing the parent node of a node and thus being able to establish a two-dimensional relational table, the tree-table structure of this scheme is usually designed to: {node_id,parent_id}, which can be described as shown in the following illustration:

Fruit

ID	Name	ID
1	Food	0
2	1
3	Red	2
4	Cherry	3
5	Yellow	2
6	Banana	5
7	Meat	1
8	Beef	7
9	Pork	7

The advantages of this scheme are obvious: design and implementation are natural, very intuitive and convenient. Disadvantages are of course also very prominent: as a direct record of the relationship between the nodes, any crud operations on the tree will be inefficient, which is mainly due to frequent "recursive" operations, the recursive process of continuous access to the database, each time the database IO has a time overhead. Of course, this approach is not useless, in the case of a relatively small tree, we can use the caching mechanism to do the optimization, the tree information loaded into memory processing, to avoid direct database IO operations performance overhead.

There are many ways to query this tree-structured data, and in Oracle we know that there is a hierarchical Queries through connect by we can easily check all the nodes under the current node. However, in the MySQL database, the current version does not support such queries.

In MySQL, you need to use recursive query, there are two methods, one is through the custom function and the combination of the Find_in_set function loop query, the other is through the stored procedure recursive search.

Method One: Use the function to get all the child node numbers.

Creates a function Getchildlst and gets a string of all the child node numbers.

Mysql> delimiter//
mysql>
mysql> CREATE FUNCTION ' getchildlst ' (Rootid INT)
    -> RETURNS (1000)
    -> BEGIN
    ->   DECLARE stemp VARCHAR (1000);
    ->   DECLARE stempchd VARCHAR (1000);
    ->
    ->   SET stemp = ' $ ';
    ->   SET stempchd =cast (Rootid as CHAR);
    ->
    ->   while stempchd isn't null do
    ->     SET stemp = concat (stemp, ', ', stempchd);
    ->     SELECT group_concat (ID) into Stempchd from Tb_test where Find_in_set (pid,stempchd) >0;
    -> end While   ;
    -> return   stemp;
    -> End
    ->//
Query OK, 0 rows Affected (0.00 sec) mysql> mysql>
;

Description: Here delimiter for the definition of the MySQL parser execution statement identifier, the default is '; ', the MySQL parser encounters '; ' to execute the statement. And in the function we need to use the '; ' but there is no hope that MySQL executes the preceding statement first, and here the identifier is changed to '//', after the function has finished executing, then changes back.

Mysql> Select Getchildlist (2);
+-----------------+
| getchildlist (2) |
+-----------------+
| $,2,3,5,4,6     |
+-----------------+
1 row in Set

mysql> select * tb_test where Find_in_set (Id,getchildlist (2));
+----+-----+--------+
| id | pid | name   |
+----+-----+--------+
|  2 |   1 | Fruit  |
|  3 |   2 | Red    |
|  4 |   3 | Cherry |
|  5 |   2 | Yellow |
|  6 |   5 | Banana |
+----+-----+--------+
5 rows in set

mysql>

Note: If the execution of the query did not result or error, the detection function

Add: Get parent node function

CREATE FUNCTION ' getparentlist ' (Rootid INT)
BEGIN 
DECLARE sparentlist varchar (1000); 
DECLARE sparenttemp varchar (1000); 
SET sparenttemp =cast (Rootid as CHAR); 
While sparenttemp isn't null do 
IF (sparentlist isn't null) THEN 
SET sparentlist = concat (sparenttemp, ', ', ', Sparen tlist); 
ELSE 
SET sparentlist = concat (sparenttemp); 
End IF; 
SELECT Group_concat (PID) into Sparenttemp from Tb_test where Find_in_set (id,sparenttemp) >0; 
End While; 
return sparentlist; 
End

Note

Query statement: Select Getparentlist (2);

SELECT * from Tb_test wherefind_in_set (Id,getparentlist (4));

Advantages: Simple, convenient, no recursive call level depth limit (max_sp_recursion_depth, Max 255);

Disadvantage: The length is limited, although can enlarge RETURNS varchar (1000), but always have the biggest limit.

Method two: Using temporary tables and procedures recursively

The stored procedures are created as follows. Createchildlst is a recursive procedure, Showchildlst prepares a temporary table and initializes it for the calling entry process. The following methods are not tested and come to Bowen:

http://wangjinlongaisong-126-com.iteye.com/blog/1188907

Mysql> delimiter//mysql> mysql> # Portal Procedure mysql> CREATE PROCEDURE showchildlst (in Rootid INT)-> BEGIN 
    -> CREATE Temporary TABLE IF not EXISTS tmplst-> (sno int primary key auto_increment,id int);
    -> DELETE from Tmplst;
    ->-> call Createchildlst (rootid,0);
    ->-> Select tmplst.*,treenodes.* from Tmplst,treenodes where tmplst.id=treenodes.id order by Tmplst.sno;
    -> end; ->//Query OK, 0 rows Affected (0.00 sec) mysql> mysql> # recursive process mysql> CREATE PROCEDURE createchildlst (in RO
    Otid int,in ndepth int)-> BEGIN-> DECLARE done INT DEFAULT 0;
    -> DECLARE b INT;
    -> DECLARE cur1 CURSOR for SELECT ID from treenodes where pid=rootid;
    -> DECLARE CONTINUE HANDLER for not FOUND SET done = 1;

    ->-> INSERT INTO tmplst values (null,rootid,ndepth);
    ->-> OPEN Cur1;
    ->-> FETCH cur1 into B; -> while doing=0 do-> call Createchildlst (b,ndepth+1);
    -> FETCH cur1 into B;
    -> End While;
    ->-> Close Cur1;
    -> end;
 ->//Query OK, 0 rows Affected (0.00 sec) mysql> delimiter;

Incoming nodes at call time

Mysql> call Showchildlst (1); +-----+------+-------+----+----------+------+
| Sno | ID | Depth | ID | nodename |
PID |   +-----+------+-------+----+----------+------+
|    4 |     1 |  0 | 1 |    A |   0 | |    5 |     2 |  1 | 2 |    B |   1 | |    6 |     4 |  2 | 4 |    D |   2 | |    7 |     5 |  2 | 5 |    E |   2 | |    8 |     3 |  1 | 3 |    C |   1 | |    9 |     6 |  2 | 6 |    F |  3 | |    10 |     7 |  3 | 7 |    G |
6 |

+-----+------+-------+----+----------+------+ 7 rows in Set (0.13 sec) Query OK, 0 rows affected, 1 warning (0.14 sec)
mysql> mysql> Call Showchildlst (3); +-----+------+-------+----+----------+------+
| Sno | ID | Depth | ID | nodename |
PID |   +-----+------+-------+----+----------+------+
|    1 |     3 |  0 | 3 |    C |   1 | |    2 |     6 |  1 | 6 |    F |   3 | |    3 |     7 |  2 | 7 |    G |
6 | +-----+------+-------+----+----------+------+ 3 rows in set (0.11 sEC) Query OK, 0 rows affected, 1 Warning (0.11 sec)
 

Depth is a depth so that you can format the program for some display. Similar to the level pseudo column in Oracle. Sno is for sort control only. This way you can also join queries with other tables in the database through a temporary table tmplst.

In MySQL, you can use system parameter max_sp_recursion_depth to control the upper limit of the number of recursive calls. The following example is set to 12.

Mysql>set max_sp_recursion_depth=12;

Queryok, 0 rows Affected (0.00 sec)

Advantages: Can be more flexible processing, and the number of layers display. And you can get the results in the tree's traversal order.

Disadvantage: Recursion has a limit of 255.

Method III: Using intermediate tables and processes

(This method is provided by yongyupost2000)

The stored procedures are created as follows. Because MySQL does not allow multiple references to temporary tables in the same statement, it is only possible to use plain table tmplst. Of course, your program is responsible for cleaning up the table after you run out of it.

Delimiter//

drop PROCEDURE IF EXISTS  showtreenodes_yongyupost2000//

CREATE PROCEDURE Showtreenodes_ yongyupost2000 (in Rootid int) The
BEGIN
 DECLARE level int;
 Drop TABLE IF EXISTS tmplst;
 CREATE TABLE tmplst (
  ID int,
  nlevel int,
  scort varchar (8000)
 );
 
 Set level=0;
 INSERT into Tmplst SELECT id,level,id from TreeNodes WHERE Pid=rootid;
 While Row_count () >0 do
  SET level=level+1;
  INSERT into Tmplst 
   SELECT a.id,level,concat (b.scort,a.id) from TreeNodes a,tmplst B 
    WHERE  a.pid=b.id and B.nlevel=level-1  ;
 End While;
  
End;
delimiter;

Call showtreenodes_yongyupost2000 (0);

After execution, a tmplst table is generated, Nlevel is the node depth, and Scort is the sorted field.

How to use

SELECT concat (Spaces (b.nlevel*2), ' +--', a.nodename) from TreeNodes a,tmplst B WHERE a.id=b.id ORDER by B.scort; +--------------------------------------------+
|
Concat (Space (b.nlevel*2), ' +--', a.nodename) | +--------------------------------------------+
|   +--a | |     +--b | |     +--d | |   +--e | |     +--c | |       +--f | | +--g | |   +--h | |   +--j | |   +--k | |     +--i | |       +--l | |       +--n | |         +--o | |         +--p | |                           +--q    |
|
+--m |
 +--------------------------------------------+ rows in Set (0.00 sec)

Advantages: The display of layer number. And you can get the results in the tree's traversal order. There is no recursive restriction.

Disadvantages: MySQL in the temporary table restrictions, can only use the ordinary table, you need to do after clean-up.

Three, schema design based on left-right value coding

In general application based on database, the requirement of query is always greater than deletion and modification. In order to avoid the "recursion" process in tree structure query, a novel left-and-right coding scheme, which has no recursive query and infinite Grouping, is designed to save the data of tree based on the first order traversal.

The details of this design can be referenced in the blog post:

http://blog.csdn.net/monkey_d_meng/article/details/6647488