Self-Referential Tables

Concepts: dmSelfReferential

Modeling Hierarchies Within a Single Table Some relationships are between rows in the same table. An employee reports to a manager who is also an employee. A category has a parent category. A comment is a reply to another comment. These are self-referential relationships, and they model hierarchies. The most common approach is the adjacency list: a parent_id column that references the same table's primary key. It is simple to implement but hard to query beyond one level. Getting all descendants of a node requires a recursive query (WITH RECURSIVE in SQL). Querying one level deep is easy: WHERE manager_id = 2 gives Bob's direct reports. Querying all of Alice's reports at every level requires a recursive CTE. On deep or wide hierarchies, recursive queries can be slow. For most data engineeri