How do you store and query hierarchical data (trees) in SQL?
How do you store and query hierarchical data (trees) in SQL?
Trees (categories, org charts, comments, folders) can be modelled in four main ways. The best choice depends on whether you read subtrees often and how often the tree moves.
| Model | Structure | Read subtree | Move node | Notes |
|---|---|---|---|---|
| Adjacency list | parent_id column |
recursive CTE | trivial | simplest; default choice with modern SQL |
| Materialized path | path = '/1/4/9/' |
LIKE '/1/4/%' |
rewrite subtree paths | easy ancestors, index prefix works |
| Nested sets | lft, rgt numbers |
range query | renumber many rows | fast reads, costly writes |
| Closure table | every ancestor-descendant pair in a second table | one join | update pairs | flexible, more storage |
CREATE TABLE categories (
id BIGINT PRIMARY KEY,
parent_id BIGINT REFERENCES categories(id),
name VARCHAR(100) NOT NULL
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.