回答。

ツリーと階層の処理は、SQLでのプログラミングにおいて一般的なケースです：商品カテゴリー、組織構造、メニューなど。最初はほとんどのDBMSが親の参照（ParentId）だけをサポートしていましたが、時間とともにネストされたセットやパス（Path）、再帰クエリを使用したCTEのような代替手段が登場しました。

従来のParent-Childアプローチの問題は、大きな階層の走査時の低速です。深さ2-3の選択は、膨大な数のJOINを引き起こします。より複雑な技術（Nested Sets、Path Enumeration）は、大量の走査を加速しますが、挿入や削除を困難にします。

解決策は、特定のタスクに適した最適なストレージモデルの選択です：

変更が少なく、大量の読み取りが行われる場合：Nested Sets（子孫を迅速に検索するためにノード内に左/右の境界を保存）
変更が頻繁に行われる場合：Adjacency List + 再帰CTE
パスの迅速な検索のために — Pathと文字列にフルパスを保持

再帰的な部分ツリーの検索の例（CTEを介して）：

WITH RecursiveTree AS (
  SELECT id, parent_id, name, 0 as level
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, rt.level + 1
  FROM categories c
  INNER JOIN RecursiveTree rt ON c.parent_id = rt.id
)
SELECT * FROM RecursiveTree;

主な特徴：

シナリオに適したストレージ構造の選択（Parent-Child、Nested Sets、Path）
任意の深さに対する再帰CTEの使用
最適なメソッドを選択するための変更と読み取りの頻度の評価

罠となる質問。

深さが固定されている場合、ツリーのストレージを単一の非正規化テーブルに置き換えることは可能ですか？

深さが小さく、常に固定されている場合（2-3レベル）に限り可能です。それを超えるとJOINが扱いきれなくなり、変更の処理が複雑になります。

大きなツリーの場合、CTEが「はまらない」ことはありませんか？再帰の深さに制限はありませんか？

はい、ほとんどのDBMSでは再帰の深さに制限が設けられています（例えば、100/32767）。1000以上のレベルのツリーの場合、深さを手動で管理するか、カスタムの走査/分割アルゴリズムが必要になります。

Nested Setsはすべての問題の解決策ですか？

いいえ、ネストされたセットは全ての子孫を瞬時に検索できますが、挿入/削除にはすべての左/右インデックスを一斉に更新する必要があり、頻繁な変更がある場合は遅くなります。

挿入の例（簡略化）：

UPDATE tree SET lft = lft + 2 WHERE lft > @parent_right;
UPDATE tree SET rgt = rgt + 2 WHERE rgt >= @parent_right;
INSERT INTO tree(id, name, lft, rgt) VALUES(@new_id, @name, @parent_right, @parent_right + 1);

一般的な間違いとアンチパターン

Parent-Childがスケーラブルであると期待する — deep treeの場合、コストが急成長
更新データに対するネストされたセット（挿入/削除） — コストが高い
大きなツリーのCTE/Stack Overflow制限の考慮不足

実際の例

ネガティブケース

大規模なインターネットストアは、隣接リストにカテゴリツリーを保持し、ネストされたサブクエリでメニューを構築していました。6以上のメニュー層では、主クエリが数分かかり、ツリーの変更はカスケード更新を引き起こしました。 利点：

シンプルなコード
標準SQLスキーマのサポート

欠点：

遅い走査、集計やレポートの構築が難しい

ポジティブケース

静的なツリー（カテゴリ）にはネストされたセットに移行し、メニューのパスにはPathを使用しました。動的シナリオにはCTEを使用しました。子孫の検索は瞬時になり、変更はバッチで実行しました。 利点：

いかなる深さのネストでも迅速な検索
柔軟性、さまざまなタスクに対して異なるモデル