Introduction

Relational databases are built around tables, but many applications need to store tree-shaped data such as categories, menus, comment threads, and organizational charts. There is no single best schema for every hierarchy. The right model depends on how often the tree changes and which operations need to be fast: inserts, moves, ancestor lookups, or subtree reads.

Adjacency List

The simplest model stores a parent_id on each row.

1CREATE TABLE categories (
2    id INT PRIMARY KEY,
3    name VARCHAR(100) NOT NULL,
4    parent_id INT NULL,
5    FOREIGN KEY (parent_id) REFERENCES categories(id)
6);

This design is easy to understand and cheap to update. Inserting a node or moving it usually means changing only one row.

The tradeoff is recursive queries. To get a full subtree or full ancestor chain, you typically need a recursive CTE.

1WITH RECURSIVE tree AS (
2    SELECT id, name, parent_id, 0 AS depth
3    FROM categories
4    WHERE id = 1
5    UNION ALL
6    SELECT c.id, c.name, c.parent_id, t.depth + 1
7    FROM categories c
8    JOIN tree t ON c.parent_id = t.id
9)
10SELECT * FROM tree;

For many systems, adjacency list plus recursive CTEs is already good enough.

Materialized Path

Materialized path stores the full path as text, such as 1/4/9.

1CREATE TABLE categories (
2    id INT PRIMARY KEY,
3    name VARCHAR(100) NOT NULL,
4    path VARCHAR(255) NOT NULL
5);

Reading a subtree becomes easy because everything under 1/4 shares a prefix.

SELECT *
FROM categories
WHERE path LIKE '1/4/%' OR path = '1/4';

This makes subtree reads simple, but moving a node means updating the path for the node and all descendants.

Nested Set

Nested set stores left and right boundary values for each node.

1CREATE TABLE categories (
2    id INT PRIMARY KEY,
3    name VARCHAR(100) NOT NULL,
4    lft INT NOT NULL,
5    rgt INT NOT NULL
6);

A subtree read is fast because descendants sit inside the numeric range.

1SELECT child.*
2FROM categories AS parent
3JOIN categories AS child
4  ON child.lft BETWEEN parent.lft AND parent.rgt
5WHERE parent.id = 1
6ORDER BY child.lft;

The cost is updates. Inserting or moving nodes may require shifting many left and right values across the table.

Closure Table

Closure table stores ancestor-descendant relationships in a separate table.

1CREATE TABLE categories (
2    id INT PRIMARY KEY,
3    name VARCHAR(100) NOT NULL
4);
5
6CREATE TABLE category_paths (
7    ancestor_id INT NOT NULL,
8    descendant_id INT NOT NULL,
9    depth INT NOT NULL,
10    PRIMARY KEY (ancestor_id, descendant_id)
11);

This makes ancestor and descendant queries fast and explicit.

1SELECT c.*
2FROM category_paths p
3JOIN categories c ON c.id = p.descendant_id
4WHERE p.ancestor_id = 1
5ORDER BY p.depth, c.id;

The tradeoff is extra storage and more work during inserts, deletes, and moves.

Which Model to Choose

A practical rule is:

• choose adjacency list when writes are common and recursive SQL is acceptable
• choose materialized path when prefix-based subtree reads matter and moves are rare
• choose nested set for read-heavy trees with infrequent structural updates
• choose closure table when ancestor and descendant queries must be fast and flexible

There is no prize for picking the most sophisticated design early. Start with the simplest model that matches your access pattern.

Modern SQL Changes the Tradeoff

Older advice often pushed developers away from adjacency lists because recursive queries were inconvenient. Modern databases with recursive CTE support make adjacency lists much more practical than they used to be.

That means you should not reach for nested sets or closure tables automatically. Only do it when the query pattern actually justifies the extra maintenance cost.

Common Pitfalls

• Picking a complex tree model before understanding whether reads or writes dominate the workload.
• Using adjacency lists without checking whether the database supports recursive CTEs well enough for the required queries.
• Choosing materialized path and then discovering that subtree moves are frequent and expensive.
• Choosing nested set for a tree that changes often, which turns updates into heavy rewrite operations.
• Forgetting that closure tables trade faster queries for more storage and more update logic.

Summary

• Hierarchical data can be stored relationally in several valid ways.
• Adjacency list is the simplest and is often enough with recursive CTE support.
• Materialized path makes subtree reads easy but makes moves more expensive.
• Nested set favors fast reads at the cost of expensive structural updates.
• Closure table is powerful for ancestor and descendant queries but needs extra tables and maintenance.