The Nested Set Model)

Principle:

   First, we should open the tree horizontally. Start from the root node ("Food") and write 1 on the left. Then write 2 to the left of "Fruit" in the tree Order (from top to bottom. In this way, you can go along the boundary of the tree (this is "traversal") and write numbers on the left and right sides of each node at the same time. Finally, we return to the root node "Food" and write 18 on the right. Below is a tree labeled with numbers, and the order of traversal is marked with arrows.

 

   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 reflect the relationship between each node on time. Because "Red" has 3 and 6 values, it is followed by a "Food" node with 1-18 values. Similarly, we can infer that all nodes with a left value greater than 2 and a right value less than 11 will be followed by "Fruit" nodes with 2 to 11. In this way, the tree structure is stored through the left and right values. This method for calculating nodes in the entire tree is called the "improved forward traversal Tree" algorithm.

Table Structure Design:

 

Common Operations:

The SQL statements for some common operations are listed below

Returns the complete Tree (Retrieving a Full Tree)

SELECTNode. name
  FROMNested_categoryNode,Nested_categoryParent
 WHERENode. lftBETWEENParent. lftANDParent. rgt
   ANDParent. name='Electronics'
 ORDERBYNode. lft

Find the Immediate Subordinates of a Node)

SELECTV .*
  FROM(SELECTNode. name,
               (COUNT (parent. name)-(AVG (sub_tree.depth)+1))Depth
          FROMNested_categoryNode,
               Nested_categoryParent,
               Nested_categorySub_parent,
               (SELECTV .*
                  FROM(SELECTNode. name,(COUNT (parent. name)-1)Depth
                          FROMNested_categoryNode,Nested_categoryParent
                         WHERENode. lftBETWEENParent. lftANDParent. rgt
                           ANDNode. name='PortableElectronics'
                         GROUPBYNode. name)V,