如何在SQL数据库中存储树-HowtostoreatreeinSQLdatabase

11  

I found the discussion in the SQL Anti-patterns very helpfull, as it also focuses on the drawbacks of every implementation.

我发现SQL反模式中的讨论非常有用，因为它也关注每个实现的缺点。

Also, The slides 48-77 in this presentation reiterate that analisys.

同样，本报告中48-77页的幻灯片重申了analisys。

Bottom line, there is no such thing as a generic tree, and no silver bullet for SQL trees. You'll have to ask yourself about the data, how and how much will they be selected, modified, will branches be moved around, etc, and based on those answers implement a suitable solution.

总之，没有通用树，也没有SQL树的银弹。你必须问问自己关于这些数据的信息，它们会被选择，修改，将会被移动，等等，并且基于这些答案来实现一个合适的解决方案。

7  

try this: Hierarchies (trees) in SQL Server 2005

试试这个:SQL Server 2005中的层次结构(树)

3  

Well, the easiest way would be for a record to have a ParentID column so that it knows which record is its parent. This is a pretty standard practice. For example, an online store might have a hierarchy of product categories. Each category will have a ParentID. Example: The "Jeans" category in an clothing database might have "Pants" as the parent category. It's a bit harder if you want a record to indicate which are its children, unless you restrict the number of children. If you want a binary tree, you could have LeftChildID and RightChildID columns. If you allow any number of children, you could have a Children column with IDs delimited by commas (such as 1,4,72,19) but that will make querying rather hard. If your database allows for array types in columns, you can probably use an array instead of a delimited string, which would be easy to query - but I'm not sure if MS SQL Server supports that or not.

好吧，最简单的方法是记录有一个ParentID列，这样它就知道哪个记录是它的父记录。这是一个相当标准的做法。例如，在线商店可能有产品类别的层次结构。每个类别都有一个父母。例如:服装数据库中的“牛仔裤”类别可能有“裤子”作为父类别。如果你想要一个记录来表明哪个是它的子节点，那就有点困难了，除非你限制了子节点的数量。如果你想要一个二叉树，你可以有LeftChildID和RightChildID列。如果您允许任意数量的子元素，您可以有一个用逗号分隔id的子元素列(例如1,4,72,19)，但是这会使查询变得非常困难。如果您的数据库允许在列中使用数组类型，那么您可能可以使用数组而不是带分隔符的字符串，这很容易查询——但我不确定MS SQL Server是否支持这一点。

Other than that, it depends on what kind of data you are modelling, and also what sort of operations you plan to do with this tree.

除此之外，它还取决于您建模的数据类型，以及您打算如何处理这棵树。

2  

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

I found that a very helpful guide.

我发现那是一本很有帮助的指南。

2  

There are two general approaches

有两种通用方法。

1. In each record, Store the Id of the parent in a nullable column (the root of the tree has no parent)
2. 在每个记录中，将父节点的Id存储在一个可空列中(树的根没有父节点)
3. Use Joe Celko's nested set model technique explained here and (thanks to comment from @onedaywhen), also in what is the original source here
4. 使用Joe Celko在这里解释的嵌套集模型技术(感谢@onedaywhen的评论)，这里的原始源代码也是如此

EDIT: new link for this material is here.

编辑:这个材料的新链接在这里。

Pros and Cons ?? !! you're kidding, right ?!

优点和缺点?! !你在开玩笑,对吧? !

1  

I have done this in the past by storing data as xml in SQL.

我过去通过将数据存储为SQL中的xml来实现这一点。