Nested set model

This is a visualization of the nested set model, an efficient method for storing and managing hierarchical data within a relational database. This model works by assigning two values, left and right, to each item. These two values define an “interval” that encompasses the item itself and all its descendants.

Core concept

Each node in the hierarchy is treated as an interval nested within its parent’s interval.

• The left value of a node is always greater than the left and smaller than the right value of its parent node.
• The right value of a node also follows the same rule.
• A node’s interval “encompasses” all the intervals of its children.

Advantages

This model is very fast for read operations (SELECT), especially for finding all descendants of a node. A single, simple query is all that’s needed:

SELECT * FROM categories WHERE left_value BETWEEN parent_left AND parent_right;

This is ideal for hierarchical data that is frequently read, such as navigation menus or product categories on a website.

Disadvantages

This model is less efficient for write operations (INSERT, UPDATE, DELETE). Each time a node is added or deleted, many left and right values of other nodes must be updated, which can be time-consuming and resource-intensive.

When to use the nested set model?

Use this model when:

• The data hierarchy rarely changes.
• High speed is required to display a sub-hierarchy or the entire data tree.

See on Wikipedia —>