Hierarchies

Which of the four tree encodings fits, and what does each pay when a subtree moves or the tree changes over time?

Balanced hierarchies with a fixed depth (year → quarter → month) simply flatten into columns (year, quarter, month) and are uninteresting. Ragged and parent-child hierarchies are different, because the schema cannot know the depth. (An org chart has some teams two levels deep and some seven.)

Four encodings

The four ways to store a tree are the read/write trade-off applied to a tree. The comparison is genuinely item by item:

Encoding	Ancestor query	Subtree move	Storage
Recursive parent pointer (`parent_id`)	iterate once per level; parallelizes badly	O(1): change one pointer	O(n)
Closure / bridge table (one row per ancestor-descendant pair)	single join	rewrite every pair under the subtree	O(n × depth)
Path enumeration string (`/1/4/9/`)	prefix scan on an ordered index	rewrite every descendant's path	O(n × depth)
Nested set (left/right numbers)	O(1) range predicate	renumber about half the tree	O(n)

-- Parent pointer: ancestors need a recursive query
WITH RECURSIVE up(id, parent_id, depth) AS (
  SELECT id, parent_id, 0 FROM org WHERE id = :team
  UNION ALL
  SELECT o.id, o.parent_id, up.depth + 1 FROM org o JOIN up ON o.id = up.parent_id
)
SELECT * FROM up;

-- Closure table: ancestors are one join
SELECT ancestor_id FROM org_closure WHERE descendant_id = :team;

Read the table as a single statement: every read-optimized tree encoding pays proportionally to subtree size on a move. The closure table is the precompute: one structural change produces many stored derived rows (the granularity-mismatch law again). Nested set is the extreme read-optimized end and is unusable anywhere the tree changes.

What the encodings do not solve: time

The hard part is that hierarchies change over time. "Report last year under the org as it was" versus "under today's org" is the as-was/as-is choice, applied to a structure instead of an attribute. A versioned hierarchy means that the closure table or the path string gains an effective interval, and every ancestor query gains a temporal predicate:

SELECT ancestor_id FROM org_closure
WHERE descendant_id = :team
  AND :as_of >= valid_from AND :as_of < valid_to;

This is the request that always arrives retroactively, after the hierarchy has already been modelled without it.