Estou curioso para saber qual é a melhor maneira (melhor prática) de lidar com hierarquias em relação ao design do banco de dados. Aqui está um pequeno exemplo de como eu costumo lidar com ele

Node Table

NodeId int PRIMARY KEY
NodeParentId int NULL
DisplaySeq int NOT NULL
Title nvarchar(255)

Ancestor Table

NodeId int
AncestorId int
Hops int

com índices em NodeId, AncestorId, Hops

s tabelas são assim:

Node Table

NodeId    NodeParentId    DisplaySeq    Title
1         NULL            1             'Root'
2         1               1             'Child 1'
3         1               2             'Child 2'
4         2               1             'Grandchild 1'
5         2               2             'Grandchild 2'

Ancestor Table

NodeId    AncestorId    Hops
1         NULL          0
1         1             0
2         1             1
2         2             0
3         1             1
3         3             0
4         1             2
4         2             1
4         4             0
5         1             2
5         2             1
5         5             0

Com esse design, descobri que, com grandes hierarquias, posso obter uma seção inteira da hierarquia muito rapidamente, ingressando na tabela Ancestor para AncestorId = target NodeId, como:

SELECT *
FROM Node n
INNER JOIN Ancestor a on a.NodeId=n.NodeId
WHERE a.AncestorId = @TargetNodeId

Também é fácil conseguir filhos diretos também

SELECT *
FROM Node n
INNER JOIN Ancestor a on a.NodeId=n.NodeId
WHERE a.AncestorId = @TargetNodeId
AND Hops = 1

Estou interessado em saber que outras soluções você pode ter usado para esse tipo de coisa. Na minha experiência, as hierarquias podem ficar bem peludas e qualquer maneira de otimizar sua recuperação é muito important