In this blog, you will study how to use adjacency list model for handling hierarchical data in MySQL.
Overview to adjacency list model
Hierarchical data is all over. It can be blog categories, product orders, or organizational structures.
There are several ways to manage hierarchical data in MySQL and the adjacency list model may be the modest solution. Because of its ease, the adjacency list model is a very popular optimal by developers and database administrators.
In the adjacency list model, each node has a pointer that points to its parent. The top node has no parent. See the subsequent categories of electronics products:
There are about terms that you should be aware with before you work with the adjacency list model:
Electronics is a top node or root node.
Laptops, Cameras & photo, Phones & Accessories nodes are the children of the Electronics node. And vice versa Electronics node is the parent of Laptops, Cameras & photo, Phones & Accessories nodes.
The leaf nodes are the nodes that have no children e.g., Laptops, PC, Android, iOS, etc while the non-leaf nodes are the ones that have at least one child.
The children and grandchildren of a node are called descendants. And the parents, grandparents, etc., of a node are also known as ancestors.
To model this category tree, we can create a table named category with three columns: id, title, and parent_id as follows:
Each row in a table is a node in the tree identified by the id column. The parent_id column is the foreign key of the category table itself. It acts like a pointer that points to the id column.
Inserting data
The root node of the tree has no parent, then, the parent_id is set to NULL. The other nodes must have one and only one parent.
To insert a root node, you set the parent_id to NULL as follows:
To insert a non-root node, you just want to set its parent_id to the id of its parent node. For example, the parent_id of Laptop & PC, Cameras & Photos and Phone & Accessories nodes are set to 1:
Finding the root node
The root node is the node that has no parent. In other words, its parent_id is NULL:
Finding the immediate children of a node
The following query gets the direct children of the root node:
Finding the leaf nodes
The leaf nodes are the nodes that have no children.
Querying the whole tree
The subsequent recursive common table expression (CTE) retrieves the whole category tree. Notice that the CTE feature has been offered since MySQL 8.0
Querying a sub-tree
The subsequent query gets the sub-tree of Phone & Accessories whose id is 7.
Querying a single path
To query a single path from bottom to top e.g., from iOS to Electronics, you use the subsequent statement:
Calculating level of each node
Suppose the level of the root node is 0, each node below has a level that equals its parent node’s level plus 1.
Deleting a node and its descendants
To delete a node and its descendants, just remove the node itself, all the descendants will be deleted automatically by the DELETE CASCADE of the foreign key constraint.
For example, to delete the Laptops & PC node and its children (Laptops, PC), you use the subsequent statement:
Deleting a node and promote its descendants
To delete a non-leaf node and promote its descendants:
First, update the parent_id of the immediate children of the node to the id of the new parent node.
Then, delete the node.
For example, to delete the Smartphones node and promote its children such as Android, iOS, Other Smartphones nodes:
First, update the parent_id for all direct children of Smartphones:
Second, delete the Smartphones node:
Both statements should be wrapped in a single transaction:
Moving a subtree
To move a subtree, just update the parent_id of the top node of the subtree. For example, to move the Cameras & photo as the children of Phone and Accessories, you use the following statement:
In this blog, you have educated how to use the adjacency list model to manage hierarchical data in MySQL.