Compare the four common ways to store a tree in a relational database — parent pointers, a materialized path string, nested sets with left/right numbering, and an ancestor-descendant pairs table — and explain how you would choose between them for a category tree that is read constantly and edited occasionally.

## The single axis Every hierarchy pattern answers one question: how much structural information do I duplicate so reads get cheaper? Parent pointers duplicate nothing and pay on every read. The other three duplicate progressively more and pay on writes. Choosing is not about elegance, it is about which side of that trade your workload sits on. ## Parent pointers (adjacency list) A nullable self-referencing `parent_id`. One row, one edge. Insert, delete and re-parent are single-row operations; a subtree move is literally one UPDATE, because descendants keep pointing at their unchanged parents. Reads of arbitrary depth require a recursive query, costing one index lookup per level. Storage is minimal and the model is fully normalized, so it cannot be internally inconsistent — the only structural risk is a cycle, which the FK does not prevent. Best when: writes are frequent, subtrees move, depth is modest, and "children of X" is the dominant read. ## Materialized path Each row carries the chain of ancestor keys as a delimited string or an array — `/1/7/23/` or `{1,7,23}`. "Everything under node 7" becomes `WHERE path LIKE '/1/7/%'`, an index range scan if the index supports prefix matching and the collation cooperates (a leading wildcard would not be indexable, which is why the delimiter and the anchored prefix matter). Ancestors are already in the string, so rendering breadcrumbs needs no extra query. Sorting siblings in tree order is a plain `ORDER BY path`. Depth is `length(path) - length(replace(path,'/',''))` or simply a stored column. Costs: the path is denormalized — the same edge is encoded in every descendant, so a move rewrites the path of the entire moved subtree (an indexed `UPDATE ... WHERE path LIKE 'old%'` with string replacement, plus index maintenance on every touched row). Path length bounds depth in practice. Deleting or renaming a key that appears inside paths is a bulk rewrite. And unless you enforce it, the path can disagree with `parent_id`. Best when: reads are overwhelmingly downward, depth is bounded, sibling ordering matters, and moves are rare. ## Nested sets Walk the tree in preorder, stamping each node with a `lft` number on the way down and an `rgt` number on the way back up. A node's descendants are exactly the rows whose `lft` falls strictly between the node's `lft` and `rgt`, so a subtree read is one index range scan with no join and no recursion. The number of descendants is `(rgt - lft - 1) / 2` — pure arithmetic. Leaves are rows where `rgt = lft + 1`. The cost is brutal on writes: inserting one node in the middle requires shifting the `lft`/`rgt` values of every node to its right, which on average is half the table. Two concurrent inserts both renumber overlapping ranges, so writers serialize and the pattern is prone to corruption if any write path forgets the renumbering. Getting the direct parent is also awkward — you need the ancestor with the largest `lft`, or a redundant `parent_id` column, which most real implementations end up keeping anyway. Best when: the tree is effectively read-only or rebuilt in bulk (a published taxonomy, a BOM snapshot), and subtree aggregate reads dominate. ## Closure / ancestor-descendant pairs table A side table of `(ancestor_id, descendant_id, depth)` covering every transitive pair plus self-rows. Downward and upward queries are both single indexed lookups because both columns are indexed; containment is one probe, so hierarchy predicates compose naturally into larger joins. Insert of a leaf costs depth + 2 rows; a subtree move costs roughly (new ancestor count × subtree size) rows deleted and inserted. Storage is O(n × average depth) — fine for balanced trees, quadratic for degenerate chains. Best when: reads dominate, *both* directions are queried (permission inheritance, org reporting), and depth is bounded. ## Choosing for a read-heavy, occasionally-edited category tree Start from the access patterns, not the pattern names: 1. **Which reads are hot?** Rendering the tree and "all products in this category and below" are downward. Breadcrumbs are upward but tiny and often cached. Permission/containment checks joined into other queries argue for closure. 2. **How often and how big are the edits?** "Occasionally" for a merchandiser dragging a category is still an interactive operation — a nested-set renumber of half the table under a lock is a bad user experience even once a day. That mostly rules out nested sets. 3. **Is depth bounded?** Category trees usually are (5–7 levels). Both path and closure are comfortable there. 4. **Can I keep one source of truth?** Yes: keep `parent_id`, and derive the read structure. Derived data can be rebuilt, verified and changed later without a data migration of the truth. The defensible answer: `parent_id` as truth, plus a materialized path column if the read set is downward-only and you want cheap tree-ordered rendering; plus a closure table instead if you need indexed upward queries or want hierarchy predicates to compose into joins the optimizer can cost. Maintain the derived structure in the same transaction as the edit — trigger or single service method — and add a reconciliation job that recomputes it from `parent_id` and alerts on drift. And the honest first move before any of this: measure whether a recursive query over an indexed `parent_id`, possibly with an application-level cache of the rendered tree, already meets the latency budget. A category tree is small, changes rarely, and caching the whole thing in memory often beats every schema trick.