sql - 处理数据库中的层次结构数据

Question

我很想知道在数据库设计方面处理层次结构的最佳方法(最佳实践)是什么。这是我通常如何处理它们的一个小例子。

节点表

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

祖先表

NodeId int
AncestorId int
Hops int

带有 NodeId、AncestorId、Hops 上的索引

表看起来像这样:

节点表

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'

祖先表

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

通过这种设计，我发现对于大型层次结构，通过加入 Ancestor 表的 AncestorId = target NodeId，我可以非常快速地获得整个层次结构部分，例如:

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

也很容易得到直接的 child

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

我很想知道您可能对此类事情使用了哪些其他解决方案。根据我的经验，层次结构可能会变得非常复杂，任何优化检索的方法都非常重要。

Answer 1

有一些特定于供应商的扩展可以做到这一点，但我最喜欢的 db-neutral 方式来自 Joe Celko - google 'Joe Celko Trees and Hierarchies' 或购买这本书:link text

这是一种非常聪明的基于集合的方法。易于查询层次结构。我添加了您拥有的“parentID”字段，因为我经常问“直接 child ”和“ parent ”问题，这可以加快速度。但这是获取“祖先”或“后代”查询的绝妙方法。