Storing hierarchical tree-like data in a database __ database

Whether you're building your own forums, posting messages on your site or writing your own CMS, you'll encounter situations where you want to store hierarchical data in a database. Also, unless you use a database like XML, the tables in the relational database are not hierarchical, they are just a flat list. So you have to find a way to transform a hierarchical database.

The storage tree structure is a common problem, and he has several solutions. There are two main methods: Adjacency list model and improved first order traversal tree algorithm

In this article, we'll explore these two ways to save hierarchical data. I'll give an example of an online grocery store tree. The Food store organizes food by category, color and variety. Tree is as follows:

This article contains examples of code to demonstrate how to save and retrieve data. I chose PHP to write examples, because I often use this language, and many people are using it or know it. You can easily translate them into your own language. adjacency list models (the adjacency list model)

The first-and most graceful-method we try is called "adjacency list Model" or "recursive method". It's a very elegant approach because you just need an easy way to iterate through your tree. In our grocery store, the table of adjacency list is as follows:

As you can see, save a "parent" node for each node. We can see that "Pear" is a child of "Green", and the latter is a child node of "Fruit", and so on. Root node, "Food", then his parent node has no value. For simplicity, I used only the "title" value to identify each node. Of course, in the actual database, you want to use the ID of the number.

Show Tree

Now that we've put the tree in the database, we have to write a display function. This function will start at the root node-no node of the parent node-and display all the child nodes of the node. For these child nodes, the function also gets and displays the child nodes of the child node. Then, for their child nodes, the function also displays all of the child nodes, and so on.

You may have noticed that the description of this function has a general pattern. We can simply write a function to get the child nodes of a particular node. This function then calls itself on each child node to display their child nodes again. This is the "recursive" mechanism, so call this method "recursive method."

<?php
//$parent is the parent node of the child node we want to view
//$level will increase as we delve into the structure of the tree/
/        to display a clear indent format
function Display_children ($parent, $level) {
    //Get all child nodes of $parent
    = mysql_query (' SELECT title from tree '.
                           ') WHERE parent= "'. $parent. '; '); 
 
    Display each node
    while ($row = Mysql_fetch_array ($result)) {
        //indent and display the title of his child node
        Echo str_repeat ("  , $level) . $row [' title ']. " \ n "; 
 
        Call this function again to significantly display_children the child node
        ($row [' title '], $level + 1);
    }
? >

To implement the entire tree, we simply call the function with an empty string as $parent and $level = 0:display_children (', 0); The function returns the tree chart of our grocery store as follows:

food<br>
  fruit<br>
    red<br>
      cherry<br>
    yellow<br>
      banana< br>
  meat<br>
    beef<br>
    Pork

Note If you only want to see a subtree, you can tell the function to start with another node. For example, to display the "Fruit" subtree, you just Display_children (' Fruit ', 0);

Path to node nodes of the path to a

With similar functions, we can also query the path of a node if you only know the name or ID of the node. For example, the path to "Cherry" is "Food" > "Fruit" > "Red". To get this path, our function obtains this path, and this function must begin at the deepest level: "cheery". But then find the parent node of the node and add it to the path. In our example, the parent node is "Red." If we know that "Red" is the parent node of "Cherry".

<?php
//$node is the name of the node to which we are looking for the path
function Get_path ($node) {
    //Find the parent node of this node
    $result = mysql_query (' SELECT parent from tree '.
                           ' WHERE title= "'. $node. '; ');
    $row = Mysql_fetch_array ($result); 
 
    Save the array
    $path = Array () in this array [5]; 
 
    If the $node is not the root node, then the last part of the path to the If
    ($row [' Parent ']!= ') {
        //  $node is the name of the $node parent node
        $path [] = $row [' Parent ']; 
 
        We want to add the path of the parent node of this node to the current path
        $path = Array_merge (Get_path ($row [' parent ']), $path);
    } 
 
    Returns the path return
    $path
;
>

This function now returns the path of the specified node.  He returns the path as an array so we can use Print_r (Get_path (' Cherry ')); To display, the result is:

Array <br>
(<br>
    [0] => Food <br>
    [1] => Fruit <br>
    [2] => Red < Br>
)

Insufficient

As we can see, this is a really good way to do it. He's easy to understand, and the code is simple. But the downside of the adjacency list model is. In most programming languages, he runs slowly and inefficiently. This is mainly due to "recursion". We have to access the database each time we query the node.

Each database query takes some time, which makes the function very slow to handle a large tree.

The second reason that this function is not too fast may be the language you are using. Unlike Lisp, most languages are not designed for recursive functions. For each node, the function calls itself, generating a new instance. So, for a 4-story tree, you might want to run 4 copies of the function at the same time. It takes a chunk of memory for each function and requires a certain amount of time to initialize, so the recursive processing of the tree is slow. improved pre-order traversal tree

Now, let's look at another way to store the tree. Recursion can be slow, so we try not to use recursive functions. We also want to minimize the number of database queries. It is best to query only once at a time.

Let's lay out the trees in a horizontal way first. Start with the root node ("Food") and write 1 on his left. Then write 2 on the left of "Fruit" in the Order of the tree (top to bottom). In this way, 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" and write 18 on the right. Below is a tree marked with numbers, and the sequence of traversal is marked with an arrow.

We call these numbers left and right (for example, the left value of "Food" is 1, and the right value is 18). As you can see, these figures are in time for the relationship between each node. Because "Red" has 3 and 62 values, it is followed by a "Food" node with a value of 1-18. Similarly, we can infer that all nodes with a left value greater than 2 and a right value of less than 11 are followed by a 2-11 "Food" node. In this way, the structure of the tree is stored through the left and right values. This method of counting the whole tree node is called an improved forward-order traversal tree algorithm.

Before continuing, let's look at the values in our table:

Note that the word "left" and "right" have special meanings in SQL. Therefore, we can only use "LfT" and "RGT" to represent these two columns. In fact, MySQL can use "'" to express, such as "left", MSSQL can be used "[]" enclosed, such as "[left]", so that it will not conflict with the keyword. Also note that we do not need the "parent" column here. We only need to use LFT and RGT to store the structure of the tree.

Get tree

If you want to display the tree by the left and right values, you first identify the nodes you want to get. For example, if you want to get the "Fruit" subtree, you want to select those nodes with a value of 2 to 11 left. expressed in SQL statements:

SELECT * from tree WHERE lft BETWEEN 2 and 11;

This will return:

All right, now the whole tree is in a query. Now it's time to show the tree like the recursive function in front of us, we're going to add an ORDER BY clause in this query. If you add and delete rows from a table, your table may be in the wrong order, so we need to sort by their left values.

SELECT * from tree WHERE lft BETWEEN 2 and one order by LfT ASC;

There is only the problem of indentation.

To display the tree structure, the child nodes should be slightly indented more than their parent node. We can save a stack by saving a right value. Each time you start from a node's child node, you add the right value of the node to the stack. You also know that the right value of the child node is smaller than the right value of the parent node, so you can tell if you are displaying the child node of the parent node by comparing the current node to the right value of the previous node in the stack. When you have finished displaying this node, you will have to remove his right value from the stack. To get the number of layers for the current node, just count the elements in the stack.

<?php
function Display_tree ($root) {
    //Get the value on the left and right of the $root node
    $result = mysql_query (' SELECT lft, RGT from Tree '.
                           ' WHERE title= "'. $root. '; ');
    $row = Mysql_fetch_array ($result); 
 
    Start with an empty $right stack
    $right = array (); 
 
    Now, get all the subsequent $result of the $root node
    = mysql_query (' SELECT title, LfT, RGT from tree '. 
 
      ') WHERE lft BETWEEN '. $row [' LfT ']. ' and '.
                           $row [' RGT ']. ' Order by LfT ASC; '); 
 
    Show each row while
  ($row = mysql_fetch_array ($result)) {//
    check stack has no element
    if (count ($right) >0) {
      // Check if we need to remove a node from the stack while
      ($right [Count ($right) -1]< $row [' RGT ']) {
        array_pop ($right);
      }
    } 
 
    Displays the indented node title
    echo str_repeat ('  , Count ($right)). $row [' title ']. " \ n "; 
 
    Add this node to the stack
    $right [] = $row [' RGT '];
  }
  ? >

If you run this code, you can get the same result as the recursive function discussed in the previous section. And this function can be a little bit quicker: he doesn't use recursion and only uses two queries.

The path of the node

With the new algorithm, we have to find a new way to get the path of the specified node. In this way, we need a list of ancestors of this node.

Because of the new table structure, it doesn't take much effort. You can look at, for example, 4-5 of the "Cherry" node, you will find that the ancestor's left value is less than 4, while the right value is greater than 5. In this way, we can use the following query:

SELECT title from the tree WHERE lft < 4 and RGT > 5 order by LfT ASC;

Note that, just like the previous query, we must sort the nodes using an ORDER BY clause. This query will return:

1 2 3 4 5 6 7

+-------+ | Title | +-------+ | Food | | Fruit | | Red | +-------+

We now have to connect the lines, we can get the "Cherry" path.

How many subsequent nodes are there. How Many descendants

If you give me a node with the left and right values, I can tell you how many subsequent nodes he has, just take a little bit of math knowledge.

Because each subsequent node adds 2 to the right value of the node in turn, the number of subsequent nodes can be computed as follows:

Descendants = (right–left-1)/2

With this simple formula, I can immediately tell you that 2-11 of the "Fruit" node has 4 subsequent nodes, 8-9 of the "Banana" node is only 1 subnodes, not the parent node.

Automated Tree Traversal

Now that you do something about the watch, we should learn how to automatically build the table. This is a good practice, first with a small tree, we also need a script to help us complete the count of nodes.

Let's start by writing a script that converts an adjacency list to a forward traversal of the tree table.

<?php
function Rebuild_tree ($parent, $left) {
    //The right value of this node is the left value plus 1
    $right = $left +1; 
 
    Gets all the child nodes of this node
    $result = mysql_query (' SELECT title from Tree '. '
                           WHERE parent= "'. $parent. '; ');
    while ($row = Mysql_fetch_array ($result)) {
        //recursive execution of this function for each child node of the current node
        //$right is the current right value, which is incremented by the Rebuild_tree function
        $ right = Rebuild_tree ($row [' title '], $right);
    } 
 
    We got the left value, and now we've processed this node we know the right value of the subnodes
    mysql_query (' UPDATE tree SET lft= '. $left. ', rgt= '
                 . $right. ' WHERE title= "'. $parent. '; '); 
 
    Returns the right value of the node +1 return
    $right +1;
}
? >

This is a recursive function. You are going from Rebuild_tree (' Food ', 1); At the beginning, this function gets all the child nodes of the "Food" node.

If there is no child node, he sets its left and right values directly. The left value is already given, 1, the right value is the left value plus 1. If there are child nodes, the function repeats and returns the last right value. This right value is used as the right value for "Food".