Managing Hierarchical Data in MySQL --- Adjacency List Model and Nested Set Model

background

In our daily development, one kind of data that we are bound to come into contact with is hierarchical data. What is hierarchical data? Business organization chart, content management categories, RBAC rights management, product categories, etc., these are hierarchical data, the following is a product category hierarchy for an electronic store:

In this article, we'll examine two models for working with hierarchical data in MySQL, starting with the traditional adjacency list model.

Adjacency List Model

Typically, the example categories shown above would be stored in a table like this (I wrote down both the CREATE and INSERT statements, you can follow along):

CREATE TABLE category(
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        parent INT DEFAULT NULL
);

INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
        (4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),
        (9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

SELECT * FROM category ORDER BY category_id;
+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)

In the adjacency list model, each item in the list contains a pointer to its parent item. The top element, in this case ELECTRONICS, has a parent element with a NULL value. The advantage of the adjacency list model is that it is relatively simple, and it is easy to see that FLASH is a child of MP3 PLAYERS, a child of PORTABLE ELECTRONICS, and a child of ELECTRONICS. The disadvantage is also obvious. We need to check all the superiors or all subordinates of a node recursively. If in a certain business scenario, the number of hierarchical layers increases to a large number and the number of leaf nodes increases, our query will become very slow. We can also optimize this by storing the path between each leaf node. But this approach increases the amount of data storage. So is there any way to store our entire data in a relational database. \

Nested Set Model

In the nested set model, we can look at our hierarchy in a new way, not as nodes and lines, but as nested containers to try to portray our electronics categories in this way:

Note that our hierarchy is still maintained because parent categories surround their subcategories. We represent this form of hierarchy in a table by using left and right values to represent the nesting of nodes:

CREATE TABLE nested_category (
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        lft INT NOT NULL,
        rgt INT NOT NULL
);

INSERT INTO nested_category VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
 (4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
 (9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);

SELECT * FROM nested_category ORDER BY category_id;

+-------------+----------------------+-----+-----+
| category_id | name                 | lft | rgt |
+-------------+----------------------+-----+-----+
|           1 | ELECTRONICS          |   1 |  20 |
|           2 | TELEVISIONS          |   2 |   9 |
|           3 | TUBE                 |   3 |   4 |
|           4 | LCD                  |   5 |   6 |
|           5 | PLASMA               |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |
|           7 | MP3 PLAYERS          |  11 |  14 |
|           8 | FLASH                |  12 |  13 |
|           9 | CD PLAYERS           |  15 |  16 |
|          10 | 2 WAY RADIOS         |  17 |  18 |
+-------------+----------------------+-----+-----+

So how do we determine the left and right values? We start numbering from the far left of the outer nodes and continue to the right:

This design can also be applied to a typical tree:

When using a tree, we go from left to right, one layer at a time, descending to each node's children before assigning right-hand numbers and moving to the right. This method is called preorder tree traversal algorithm.

retrieve complete tree

We can retrieve the full tree by using a self-join that connects the parent to the node, based on a node's lft value that will always appear between its parent's lft and rgt values:

SELECT node.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;

+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| TELEVISIONS          |
| TUBE                 |
| LCD                  |
| PLASMA               |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
| CD PLAYERS           |
| 2 WAY RADIOS         |
+----------------------+

Unlike our previous example using an adjacency list model, this query will work regardless of the depth of the tree. We don't care about the rgt value of the node in the BETWEEN clause, because the rgt value will always be within the same parent node as the lft value.

refer to

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/