In this lesson, you will learn how to structure data that has a "parent-child" hierarchy, such as an employee reporting structure, using only flat relational tables.
The Adjacency List
The most common way to represent a hierarchy is the Adjacency List. In this approach, you add a parent_id column to your table that refers back to the primary_key of the same table. This is called a recursive relationship because a row relates to another row in the same entity.
Imagine an employees table:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);
In this setup, if "Alice" (ID 1) is the CEO, her manager_id is NULL. If "Bob" (ID 2) reports to Alice, his manager_id is set to 1. This is excellent for simple lookups (e.g., "Who is Bob's boss?"), but it can become expensive to query if you need to find an entire chain of command.
The Closure Table
If your business needs require frequent "deep" queries—like finding everyone who reports to a manager at any level—an Adjacency List can become inefficient. A Closure Table solves this by storing every path in the hierarchy in a separate mapping table. Instead of just storing the immediate parent, you store every ancestor-descendant relationship.
| ancestor_id | descendant_id | depth |
|---|---|---|
| 1 | 2 | 1 |
| 1 | 3 | 2 |
| 2 | 3 | 1 |
Here, row 2 shows that Employee 1 is an ancestor of Employee 3 with a depth of 2. While this requires more space and logic to maintain (you must update the closure table whenever a relationship changes), it allows you to retrieve an entire tree branch with a single simple SELECT statement, avoiding the complexity of recursive logic in your queries.
Choosing between these methods depends on your read-to-write ratio: Adjacency lists are easy to maintain, while closure tables are optimized for deep, complex tree traversals.
Key Takeaway: Recursive relationships allow us to model complex hierarchies within a flat schema by either pointing to a direct parent (Adjacency List) or explicitly mapping all descendant paths (Closure Table).