blocks|key|1542987|text|看看Managing+Hierarchical+Data+in+MySQL吧。它讨论了在关系数据库中存储和管理分层(树形)数据的两种方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1542988|第一种方法是邻接列表模型，这是您本质上所描述的:拥有一个引用表本身的外键。虽然这种方法很简单，但对于某些查询来说效率可能非常低，比如构建整个树。|1542989|本文讨论的第二种方法是嵌套集合模型。这种方法的效率和灵活性要高得多。有关详细解释和示例查询，请参阅该文章。|1542990|entityMap|0|LINK|mutability|MUTABLE|url|http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/^0|2|Z|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

Have a look at <a href="http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/" rel="noreferrer">Managing Hierarchical Data in MySQL</a>. It discusses two approaches for storing and managing hierarchical (tree-like) data in a relational database.

The first approach is the adjacency list model, which is what you essentially describe: having a foreign key that refers to the table itself. While this approach is simple, it can be very inefficient for certain queries, like building the whole tree.

The second approach discussed in the article is the nested set model. This approach is far more efficient and flexible. Refer to the article for detailed explanation and example queries.

blocks|key|1543026|text|对我来说，拥有一个带有外键的表是有意义的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1543027|然后，您可以使用SQL中的公用表表达式或Oracle中的connect+by+prior语句来构建树。|1543028|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Having a table with a foreign key to itself does make sense to me. 

You can then use a common table expression in SQL or the connect by prior statement in Oracle to build your tree.

blocks|key|1327825|text|您提到了最常用的实现，即邻接表：https://blogs.msdn.microsoft.com/mvpawardprogram/2012/06/25/hierarchies-convert-adjacency-list-to-nested-sets|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1327826|还有其他模型，包括物化路径和嵌套集：http://communities.bmc.com/communities/docs/DOC-9902|1327827|Joe+Celko写了一本关于这个主题的书，从一般SQL的角度来看，这是一个很好的参考(在上面的嵌套set文章链接中提到了它)。|1327828|此外，Itzik+Ben-Gann在他的“Microsoft+SQL+Server2005内幕:+T-SQL查询”一书中对最常见的选项进行了很好的概述。|1327829|选择模型时要考虑的主要事项是：|1327830|1)结构更改的频率-树的实际结构更改的频率。一些模型提供了更好的结构更新特性。但是，将结构更改与其他数据更改分开是很重要的。例如，您可能希望对公司的组织结构图进行建模。有些人会将其建模为邻接列表，使用员工ID将员工链接到其主管。这通常是一种次优的方法。一种通常效果更好的方法是，将组织结构与员工本身分开建模，并将员工作为结构的一个属性进行维护。这样，当员工离开公司时，组织结构本身不需要改变，只需要与离开的员工关联即可。|1327831|2)树是以写入为主还是以读取为主-一些结构在读取结构时工作得很好，但在写入结构时会产生额外的开销。|1327832|3)您需要从结构中获取哪些类型的信息-某些结构擅长提供有关结构的某些类型的信息。示例包括查找节点及其所有子节点，查找节点及其所有父节点，查找满足特定条件的子节点的数量，等等。您需要知道需要从结构中获得哪些信息，以确定最适合您需要的结构。|1327833|entityMap|0|LINK|mutability|MUTABLE|url|https://blogs.msdn.microsoft.com/mvpawardprogram/2012/06/25/hierarchies-convert-adjacency-list-to-nested-sets|1|http://communities.bmc.com/communities/docs/DOC-9902^0|G|31|0|0|I|1G|1|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@$A|12|B|13|1|14]]|C|$]]|$1|D|3|E|5|6|7|15|8|@]|9|@$A|16|B|17|1|18]]|C|$]]|$1|F|3|G|5|6|7|19|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|1A|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|1B|8|@]|9|@]|C|$]]|$1|L|3|M|5|6|7|1C|8|@]|9|@]|C|$]]|$1|N|3|O|5|6|7|1D|8|@]|9|@]|C|$]]|$1|P|3|Q|5|6|7|1E|8|@]|9|@]|C|$]]|$1|R|3|-4|5|6|7|1F|8|@]|9|@]|C|$]]]|S|$T|$5|U|V|W|C|$X|Y]]|Z|$5|U|V|W|C|$X|10]]]]

You mention the most commonly implemented, which is Adjacency List:
<a href="https://blogs.msdn.microsoft.com/mvpawardprogram/2012/06/25/hierarchies-convert-adjacency-list-to-nested-sets" rel="noreferrer">https://blogs.msdn.microsoft.com/mvpawardprogram/2012/06/25/hierarchies-convert-adjacency-list-to-nested-sets</a>

There are other models as well, including materialized path and nested sets:
<a href="http://communities.bmc.com/communities/docs/DOC-9902" rel="noreferrer">http://communities.bmc.com/communities/docs/DOC-9902</a>

Joe Celko has written a book on this subject, which is a good reference from a general SQL perspective (it is mentioned in the nested set article link above).

Also, Itzik Ben-Gann has a good overview of the most common options in his book "Inside Microsoft SQL Server 2005: T-SQL Querying".

The main things to consider when choosing a model are:

1) Frequency of structure change - how frequently does the actual structure of the tree change. Some models provide better structure update characteristics. It is important to separate structure changes from other data changes however. For example, you may want to model a company's organizational chart. Some people will model this as an adjacency list, using the employee ID to link an employee to their supervisor. This is usually a sub-optimal approach. An approach that often works better is to model the org structure separate from employees themselves, and maintain the employee as an attribute of the structure. This way, when an employee leaves the company, the organizational structure itself does not need to be changes, just the association with the employee that left.

2) Is the tree write-heavy or read-heavy - some structures work very well when reading the structure, but incur additional overhead when writing to the structure.

3) What types of information do you need to obtain from the structure - some structures excel at providing certain kinds of information about the structure. Examples include finding a node and all its children, finding a node and all its parents, finding the count of child nodes meeting certain conditions, etc. You need to know what information will be needed from the structure to determine the structure that will best fit your needs.

blocks|key|1327894|text|如果你必须使用关系型DataBase来组织树形数据结构，那么Postgresql有一个很酷的ltree模块，它提供了数据类型，用于表示存储在分层树形结构中的数据的标签。你可以从那里得到想法。(有关更多信息，请参阅：http://www.postgresql.org/docs/9.0/static/ltree.html)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1327895|通常，LDAP用于按层次结构组织记录。|1327896|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/9.0/static/ltree.html^0|2Z|1G|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

If you have to use Relational DataBase to organize tree data structure then Postgresql has cool ltree module that provides data type for representing labels of data stored in a hierarchical tree-like structure. You can get the idea from there.(For more information see: <a href="http://www.postgresql.org/docs/9.0/static/ltree.html">http://www.postgresql.org/docs/9.0/static/ltree.html</a>)

In common LDAP is used to organize records in hierarchical structure.

What would be the best way to implement a customizable tree data structure (meaning, a tree structure with an unknown number of level) in a database?
I've done this once before using a table with a foreign key to itself.
What other implementations could you see, and does this implementation make sense?

Database Structure for Tree Data Structure

数据结构

SQL Server

数据库

在数据库中实现可定制的树数据结构(即具有未知层数的树结构)的最佳方式是什么？在使用带有外键的表之前，我曾经这样做过一次。你还能看到其他什么实现，这个实现有意义吗？

问树型数据结构的数据库结构
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问树型数据结构的数据库结构EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问树型数据结构的数据库结构
EN