I have a number of distinct items stored in different MySQL tables, which I'd like to put in a tree hierarchy. Using the adjacency list model, I can add a parent_id field to each table and link the tables using a foreign key relationship.
我有许多不同的项存储在不同的MySQL表中,我想将它放在树层次结构中。使用邻接列表模型,我可以向每个表添加parent_id字段,并使用外键关系链接表。
However, I'd like to use a nested sets/modified preorder tree traversal model. The data will be used in a environment that's heavily biased towards reads, and the kind of queries I expect to run favour this approach.
但是,我想使用嵌套集/修改的预订树遍历模型。这些数据将在一个严重偏向于读取的环境中使用,而我期望运行的查询类型也支持这种方法。
The problem is that all the information I have on nested sets assumes that you only have one type of item, stored in a single table. The ways round this that I can think of are:
问题是我在嵌套集上的所有信息都假定您只有一种类型的项,存储在一个表中。我能想到的方法是:
- Having multiple foreign key fields in the tree, one for each table/item type.
- Storing the name of the item table in the tree structure as well as the item ID.
树中有多个外键字段,每个表/项类型一个。
在树结构中存储项目表的名称以及项目ID。
Both approaches are inelegant to say the least, so is there a better way of doing this?
这两种方法至少可以说是不优雅的,所以有更好的方法吗?
2 个解决方案
#1
RDBMS are not a good match to storing hierarchies to begin with, and your use case makes this even worse. I think a little more fine tuned but still ugly variations of your own suggestions are what you are going to get using a RDBMS. IMHO other data models would provide better solutions to your problem, like graph databases or maybe document databases. The article Should you go Beyond Relational Databases? gives a nice introduction to this kind of stuff.
RDBMS不是一个很好的匹配存储层次结构的开头,而你的用例使这更糟糕。我认为你可以使用RDBMS获得一些更好的调整,但仍然是你自己建议的丑陋变化。恕我直言,其他数据模型将为您的问题提供更好的解决方案,如图形数据库或文档数据库。你应该超越关系数据库吗?给出了这种东西的一个很好的介绍。
#2
You have have several types of tree, and a single table which contains the tree information (i.e. the left/right values) for all tree types?
您有几种类型的树,以及包含所有树类型的树信息(即左/右值)的单个表?
If you have have several types of tree, why not a different table for each type?
如果您有几种类型的树,为什么不为每种类型使用不同的表?
#1
RDBMS are not a good match to storing hierarchies to begin with, and your use case makes this even worse. I think a little more fine tuned but still ugly variations of your own suggestions are what you are going to get using a RDBMS. IMHO other data models would provide better solutions to your problem, like graph databases or maybe document databases. The article Should you go Beyond Relational Databases? gives a nice introduction to this kind of stuff.
RDBMS不是一个很好的匹配存储层次结构的开头,而你的用例使这更糟糕。我认为你可以使用RDBMS获得一些更好的调整,但仍然是你自己建议的丑陋变化。恕我直言,其他数据模型将为您的问题提供更好的解决方案,如图形数据库或文档数据库。你应该超越关系数据库吗?给出了这种东西的一个很好的介绍。
#2
You have have several types of tree, and a single table which contains the tree information (i.e. the left/right values) for all tree types?
您有几种类型的树,以及包含所有树类型的树信息(即左/右值)的单个表?
If you have have several types of tree, why not a different table for each type?
如果您有几种类型的树,为什么不为每种类型使用不同的表?