Advanced Schema PatternsLesson 4.1

How to model hierarchical data in a relational database

adjacency list, self-referencing FK, recursive CTE, nested set model, closure table, choosing a hierarchy pattern, tree traversal query

The Hierarchy Problem

Categories, organizational charts, comment threads, and file systems are all hierarchical. Relational databases store flat rows. You need a pattern to bridge this.

Adjacency List

The simplest approach: add a parent_id column that references the same table.

CREATE TABLE categories (
  category_id INT PRIMARY KEY AUTO_INCREMENT,
  name        VARCHAR(100) NOT NULL,
  parent_id   INT NULL,
  FOREIGN KEY (parent_id) REFERENCES categories(category_id)
);

-- Electronics → Mobile → Smartphones
INSERT INTO categories VALUES (1,'Electronics',NULL);
INSERT INTO categories VALUES (2,'Mobile',1);
INSERT INTO categories VALUES (3,'Smartphones',2);

Querying with a Recursive CTE

-- Find all descendants of Electronics (id=1)
WITH RECURSIVE subtree AS (
  SELECT category_id, name, parent_id
  FROM categories WHERE category_id = 1
  UNION ALL
  SELECT c.category_id, c.name, c.parent_id
  FROM categories c
  JOIN subtree s ON c.parent_id = s.category_id
)
SELECT * FROM subtree;

When to Use Other Patterns

Closure table: when you need fast arbitrary-depth queries without recursion — stores all ancestor-descendant pairs.
Nested set: when the tree is read-heavy and rarely updated — fast subtree reads, expensive updates.

Adjacency list with recursive CTE covers most cases and is the easiest to maintain.