处理数据库中的层次结构数据 [英] Handling Hierarchy Data in Database

问题描述

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

I'm curious to know what the best way (best practice) to handle hierarchies are in regards to database design. Here is a small example of how I usually handle them.

节点表

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

祖先表

NodeId int
AncestorId int
Hops int

在NodeId，AncestorId，Hops上具有索引

with Indexes on 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 = target NodeId的Ancestor表，可以非常快速地获取层次结构的整个部分，例如:

With this design, I've found that with large hierarchies I can get an entire section of the hierarchy very quickly by joining on the Ancestor table for AncestorId = target NodeId, like:

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

我有兴趣知道您可能还针对此类事情使用了哪些其他解决方案.以我的经验，层次结构可能会变得很繁琐，而任何优化其检索的方法都非常重要.

I'm interested in knowing what other solutions you may have used for this type of thing. In my experience, hierarchies can get pretty hairy, and any way to optimize their retrieval is very important.

推荐答案

正如MarkusQ和n8wrl所指出的那样，Joe Celko在这方面有一些不错的东西.我要补充一点，有多种方法可以对层次结构进行建模(Joe的书包含了许多我相信的内容，而不仅仅是他认为最好"的一种方法).您的最终决定将希望考虑您自己的特定需求.建模的一些不同方法中，某些方法更适合于写密集型操作，而另一些方法则适合于频繁或快速地上下读取层次结构.只需记住您的系统将使用它做什么.

As MarkusQ and n8wrl have already pointed out, Joe Celko has some good stuff on this. I'll just add that there are multiple ways to model a hierarchy (Joe's book contains several I believe, not just one that he considers the "best"). Your final decision will hopefully take into account your own specific needs. Some of the different ways to model it are better for write-intensive operations while others are better for frequent or fast reads up and down the hierarchy. Just keep in mind what your system will be doing with it.

这篇关于处理数据库中的层次结构数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！