[Database] database storage Hierarchical Data

Whether you want to build your own forum, publish messages from an email list on your website, or write your own cms: you will encounter a situation: store hierarchical data in a database. Unless you use a database similar to xml, it is difficult for a general relational database to do this. Tables in relational databases are not layered; they are just a simple

Whether you want to build your own forum, publish messages from an email list on your website, or write your own cms: you will encounter a situation: store hierarchical data in a database. Unless you use a database similar to xml, it is difficult for a general relational database to do this. Tables in relational databases are not layered; they are just a simple

Whether you want to build your own forum, publish messages from an email list on your website, or write your own cms: you will encounter a situation: store hierarchical data in a database. Unless you use a database similar to xml, it is difficult for a general relational database to do this. Tables in relational databases are not hierarchical; they are just a simple list. You must find a way to convert the hierarchical data into a simple file data.

The storage tree is a common problem that can be implemented using multiple solutions. There are two main methods: the adjacent table model and the algorithm for pre-tree traversal after modification.

In this article, we will explore these two methods to store hierarchical data. I will use this fictitious tree from a fictitious online food store as an example. This Food Store organizes its food by category, color, and category.

This tree is as follows:

This article contains code examples to show how to save and retrieve data. In the original article, PHP is used. Here I will use java to demonstrate its code display.

[1] adjacent List Model

We first try or use the most elegant method. We call it the "adjacent table mode" or we call it the "recursive method ".

This is an elegant method, because you only need a simple function to traverse the tree. In our food store example, the list of adjacent tables can be expressed as follows:

As you can see, in the method of the adjacent table, you only save the parent node of each node. We can clearly see from the table that "Pear" is a child of "Green" and "Green" is also a child of "Fruit.

The root node "Food" does not have a parent node. For simplicity, I used the "title" value to determine each node. Of course, in a real database, you can use id to mark it.

Give Me the Tree

Since we have inserted our tree in the database, it is time to write a display function. This function will have to start from the root node (no parent node) and should display all the children of this node. For these child nodes, this function should retrieve and display all child nodes of these child nodes. The child nodes are displayed recursively.

public void DisplayTree(int parentId,int level){String sql = "select NodeId,NodeName,ParentId from Tree where parentid="+ parentId;conn = sqlManager.GetConn();try {statement = conn.createStatement();ResultSet rs = statement.executeQuery(sql);while(rs.next()){for(int i = 0;i < level*2;i++){System.out.print("-");}System.out.println(rs.getString("NodeName"));DisplayTree(rs.getInt("NodeId"),level+1);}} catch (SQLException e) {e.printStackTrace();}}

Note: The sqlManager class is a database operation class.

Data stored in the database:

Print the entire tree

DisplayTree (0, 0)

Result:

If you only want to see a subtree, you can tell the Id of the Start Node of the function.

For example, display the "Fruit" subtree and run DisplayTree );

The Path to a Node

Sometimes we need to know the path of a node. For example, the path of "Cherry" is Food> Fruit> Red> Cherry. Here, we can start from Cherry and then recursively query the nodes before the query node until the parent node ID of a node is 0.

// Obtain the public ArrayList path of a node
 
  
GetPath (int nodeId) {String SQL = "select ParentId, NodeName from Tree where NodeId =" + nodeId; ArrayList
  
   
Path = new ArrayList
   
    
(); Try {conn = sqlManager. getConn (); statement = conn. createStatement (); ResultSet rs = statement.exe cuteQuery (SQL); rs. next (); int parentId = rs. getInt ("ParentId"); String nodeName = rs. getString ("NodeName"); if (parentId! = 0) {path. addAll (GetPath (parentId);} path. add (nodeName);} catch (SQLException e) {e. printStackTrace ();} return path ;}
   
  
 

ArrayList
 
   path = main.GetPath(9);int index = 0;for (String node : path) {if(index != 0){System.out.print("->");}System.out.print(node);index++;}
 

Id = 9 corresponds to the Banana path: Food-> Fruit-> Yellow-> Banana advantages and disadvantages. We can see that using the adjacent table model is indeed a good method. It is easy to understand and easy to write the implemented code. So what are the disadvantages? That is, in most languages, the execution efficiency of the adjacent table model is low and slow. This is mainly caused by recursion. When we query every node in the tree, we need to query the database in sequence. Because each query takes some time, this makes the function very slow when processing big trees.

The second reason is that the method in the programming language you may use is not so fast ,. Most programming languages except Lisp are not designed for recursion. When the depth of a node is 4, it must generate four function instances at the same time, which all take time and occupy a certain amount of memory space. Therefore, the low efficiency of the adjacent table model can be imagined.

[2] algorithm deformation of pre-tree traversal

Let's look at another method to store the tree structure. As mentioned earlier, we hope to reduce the number of queries. It is best to query the database only once.

Now we can place the tree horizontally. As shown in, we start from the root node ("Food"), mark "1" on its left, then we go to "Fruit", and Mark "2" on the left ", then, the tree is traversed in the ascending order, and numbers are marked on the left and right of each node. The last number is written on the right of the "Food" node. In this image, you can see the entire tree marked with numbers and several arrows to indicate the order of numbers.

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 numbers represent the relationship between each node.

For example, the number on the left of the "Red" node is 3, and the number on the right is 6. It is the descendant of Food (1-18. Similarly, we can note that nodes with a left value greater than 2 and a right value less than 11 are descendants of "Fruit. Now, all nodes are stored in the left-right mode, this method is called the modified forward traversal algorithm by traversing a tree and marking the left and right numbers for each node.

Before we proceed, let's look at these values in our table:

Note that "Left" and "Right" have special meanings in SQL. Therefore, we must use "lft" and "rgt" to identify columns. Note that we do not really need the "parent" column. We now have the lft and rgt value storage tree structures.

Retrieve the Tree if you want to use a table with the left and right values to display the Tree and, you must first determine the node you want to Retrieve. For example, if you want to retrieve the "Fruit" subtree, You have to select the nodes with the left value between 2 and 11. SQL statement:

SELECT * FROM FoodTree WHERE Lft BETWEEN 2 AND 11;

Note: FoodTree is a table that stores data.

Return Value:

The entire tree only needs to be queried once.

This tree is like a recursive function. We have to add the order by clause to the query statement. If you add or delete rows from your table, your table may not be stored in the correct order. We should sort by the Left value.

SELECT * FROM FoodTree WHERE Lft BETWEEN 2 AND 11 ORDER BY Lft ASC;

The only problem is indentation.

The tree structure is displayed, and children should be indented slightly higher than their parents.

Just as we often think of a solution for tree problems-stack. Here, we can maintain a stack that only saves the right number. We know that the Rgt value of all the children of the node is smaller than the parent Rgt value, so we can compare the Rgt value of the current node and the Rgt value of the last node in the stack. When the Rgt value of the current node is greater than the value of the top element of the stack (indicating that the sub-tree of the top element of the stack is traversed), the top value of the stack is displayed. Check the stack top value for recycling until the stack top value is smaller than the Rgt value of the current query node. In this case, you only need to check the elements in the stack. The number of elements indicates the number of ancestor nodes (set to n) of the current query node ). Print n spaces. The Code is as follows: