Storing hierarchical data, such as tree structures, in a relational database can be achieved through various models. Each has its own characteristics, benefits and drawbacks. Here are three common approaches.
Adjacency List Model
The simplest way to represent tree-like data. Add a parent_id column to each row referencing the id of the parent node. Straightforward, but less efficient as the tree gets deeper.
CREATE TABLE tree (
node_id INT PRIMARY KEY,
parent_id INT NULL REFERENCES tree (node_id)
);Pros
- Simple implementation.
- Suitable for shallow trees.
Cons
- Query performance degrades with tree depth.
- No built-in support for traversing beyond immediate children.
Path Enumeration
Instead of storing only the parent ID, store the entire path from the root to the current node. Great for pulling all ancestors of a node at once; painful on updates, since moving a node means rewriting every path that contains it.
CREATE TABLE tree (
node_id INT PRIMARY KEY,
path VARCHAR(255) -- "1.2.3" = node 3 under node 2 under node 1
);Pros
- Efficient for ancestor and subtree queries.
Cons
- Updates are complex and potentially costly.
Nested Set Model
Represent the tree as nested sets: each node gets a left and right value marking its position. Supports efficient range queries, but insertions and deletions require carefully renumbering those values.
CREATE TABLE tree (
node_id INT PRIMARY KEY,
left_value INT,
right_value INT
);Pros
- Efficient range queries and subtree reads.
Cons
- Left/right values must be recalculated on every structural change.
Conclusion
The right model depends on the depth of your tree, the queries you run most, and how much update complexity you can stomach. Each one trades simplicity, query efficiency and maintenance overhead against the others.