Modeling Hierarchical Data

Represent organizational trees and categories within a flattened graph structure.

In this lesson, you will learn how to represent parent-child relationships—like organizational charts or product categories—within a flat, relational database structure.

While graph databases excel at complex traversal, most production environments rely on relational tables. Modeling hierarchical data in a flat schema requires balancing read performance against structural integrity.

The Adjacency List Pattern

The most common approach is the Adjacency List. In this model, every record stores a reference to its immediate parent. This is intuitive and highly flexible for adding or moving nodes.

Consider an employees table:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

In this structure, a CEO has a NULL manager_id, while every other employee points to their direct supervisor.

Flattening the Graph

While the Adjacency List is simple, retrieving an entire subtree (e.g., "everyone who reports to the VP of Engineering") becomes expensive in standard SQL because it requires recursive Common Table Expressions (CTEs).

To optimize for deep hierarchical queries, we often flatten the representation using a technique like Path Enumeration. Instead of just storing the direct parent, you store the entire "ancestry path" as a string or array:

id	name	path
1	CEO	/1/
2	VP Eng	/1/2/
3	Dev Mgr	/1/2/3/

By storing the path, you transform a recursive tree traversal into a simple string search. You can find everyone under the VP of Engineering simply by querying: SELECT * FROM employees WHERE path LIKE '/1/2/%';

Choosing Your Strategy

The trade-off is clear: the Adjacency List is best for write-heavy systems where relationships change frequently (you only update one row). Path Enumeration is superior for read-heavy systems where you frequently need to materialize entire hierarchies or branches without the performance overhead of complex recursive joins.

Key Takeaway: Choose the Adjacency List for simplicity and frequent updates, or Path Enumeration to flatten the hierarchy and optimize read-heavy subtree traversals.