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

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

节点表

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

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

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

获得直系孩子也很容易

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

我有兴趣知道您可能还使用过哪些其他解决方案来解决此类问题。根据我的经验，层次结构可能会变得非常复杂，任何优化其检索的方法都非常重要。

有一些特定于供应商的扩展可以做到这一点，但我最喜欢的数据库中立方式来自 Joe Celko - 谷歌“Joe Celko Trees and Hierarchies”或购买这本书：链接文本 https://rads.stackoverflow.com/amzn/click/com/1558609202

这是一种非常聪明的基于集合的方法。易于查询层次结构。我添加了“parentID”字段，只是因为我经常询问“直接子代”和“父代”问题，这会加快这些问题的速度。但这是获取“祖先”或“后代”查询的好方法。