Managing Hierarchical Department Data with the Nested Set Model in MySQL

Traditional tree storage using a parent_id column works for small datasets but becomes inefficient as the hierarchy grows; the article introduces the nested set model, which assigns each node a left (lft) and right (rgt) value to represent its position in the tree.

By storing lft and rgt, all descendants of a node can be retrieved with a simple BETWEEN query on the lft column. Example:

SET @lft := 9;
SET @rgt := 18;
SELECT * FROM department WHERE lft BETWEEN @lft AND @rgt ORDER BY lft ASC;
/* The query returns the target department and all its descendants */

The total number of descendants can be calculated without a separate count query using the formula (rgt - lft - 1) / 2. Example:

-- Example calculation
SELECT (18 - 9 - 1) / 2 AS descendant_count;  -- returns 4

A node is a leaf when its right value is exactly one greater than its left value (rgt - 1 = lft). This check replaces the need for an extra isLeaf column.

Inserting a new department requires shifting the lft and rgt values of all nodes that appear after the insertion point by 2, typically wrapped in a transaction:

SET @lft := 7;  /* new node left */
SET @rgt := 8;  /* new node right */
BEGIN;
UPDATE department SET lft = lft + 2 WHERE lft > @lft;
UPDATE department SET rgt = rgt + 2 WHERE rgt >= @lft;
INSERT INTO department (name, lft, rgt, level) VALUES ('New Dept', @lft, @rgt, 5);
COMMIT;

Deleting a department mirrors the insert logic, decreasing the subsequent lft/rgt values by 2 and then removing the target row:

SET @lft := 7;
SET @rgt := 8;
BEGIN;
UPDATE department SET lft = lft - 2 WHERE lft > @lft;
UPDATE department SET rgt = rgt - 2 WHERE rgt > @lft;
DELETE FROM department WHERE lft = @lft AND rgt = @rgt;
COMMIT;

Other common operations include retrieving direct children, ancestor paths, and specific levels using combinations of lft, rgt, and level values, each demonstrated with concise SQL snippets.

A JavaScript example shows how to reconstruct the hierarchical tree in memory from a flat list of nodes that contain lft, rgt, and level, using a stack‑like array to track right boundaries and assign parent_id and leaf status.

The only drawback noted is that every insert or delete requires updating the left/right values of all subsequent nodes, which can be costly for very large trees.