分层数据 - 嵌套集模型：MySql
我刚刚学习如何实现嵌套集模型，但仍然对其涉及可能属于多个类别的项目的某些方面感到困惑。鉴于下面的示例是从 此处 中提取的，并且反映了我遇到的许多其他示例...
添加苹果时如何避免数据库中的重复，因为它们是多种颜色的（即红色、黄色、绿色）？
I am just learning how to implement the Nested Set Model but still have confusion with a certain aspect of it involving items that may be part of multiple categories. Given the example below that was pulled from HERE and mirrors many other examples I have come across...
How do you avoid duplication in the DB when you add Apples since they are multi-colored (i.e. Red, Yellow, Green)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您无法避免重复，并且苹果（或对苹果的引用）将在您的树中放置两次，否则它不会是一棵树，而是一个图。如果您构建... Swing JTree 或 HTML 树，您的问题同样适用；)。
嵌套集合模型只是一种在关系数据库中推送和遍历树结构的有效方法。它本身并不是一种数据结构。它在 MySQL 用户中更受欢迎，因为 MySQL 缺乏处理树结构的功能（例如 Oracle 提供的功能）。
干杯!
You do not avoid duplications and the apple (or a reference to the apple) will be placed twice in your tree otherwise it won't be a tree but rather a graph. Your question is equally applicable if you build a... Swing JTree or an HTML tree ;).
The nested set model is just an efficient way to push and traverse a tree structure in a relational DB.It is not a data structure itself. It's more popular among MySQL users since MySQL lacks functionality for processing tree structures (e.g. like the one that Oracle provides).
Cheers!
嵌套集模型是一种 1:N（一对多）关系的结构，您想要使用 M:N（多对多）关系（许多项目可以有 apple 作为父项，但可以有多个父项）。
查看这篇文章
Wikipedia
但您应该意识到，分层 M:N 关系很快就会变得非常复杂！
Nested set model is a structure for 1:N (one-to-many) relationships, you want to use M:N (many to many) relationship (many items can have apple as parent, but can have more than one parent).
See this article
Wikipedia
But you should be aware, that hierarchical M:N relationships can get quite complex really fast!
在这里大声思考，但也许将某些属性（如红色、黄色和绿色）视为“标签”而不是“类别”并用单独的逻辑处理它们会有所帮助。这将使您保留嵌套集模型并避免不必要的重复。另外，它还可以让您的类别更加简单。
这完全取决于您如何看待信息。类别只是表示属性的另一种方式。我知道你的例子只是为了说明目的，但如果你要按颜色对水果进行分类，为什么你不以同样的方式对肉类进行分类，即白肉和红肉？你很可能不会。所以我的观点是，可能也没有必要按颜色对水果进行分类。
相反，某些属性可以通过其他方式更好地表示。事实上，以最简单的形式，它可以记录为“食物”表中标记为“颜色”的列。或者，如果它是一个非常常见的属性，并且您发现自己显着重复了该值，则可以将其拆分到一个名为“颜色”的单独表中，并映射到第三个表中的每个食品项目。当然，更抽象的方法是将表格概括为“标签”，并将每种颜色作为单独的标签，然后可以映射到任何食品。然后，您可以将任意数量的标签（颜色）映射到任意数量的食品，从而为您提供真正的多对多关系，并释放您的类别名称以使其更加通用。
我知道关于标签是类别还是类别是标签等问题一直存在争论，但这似乎是它们可以互补并创建一个更抽象、更强大、更易于管理的系统的一个例子。
Thinking out loud here, but perhaps it would be helpful to view some attributes (like Red, Yellow and Green) as 'tags' instead of 'categories' and handle them with separate logic. That would let you keep the Nested Set model and avoid unnecessary duplication. Plus, it would allow you to keep your categories simpler.
It's all in how you think about the information. Categories are just another way of representing attributes. I understand your example was just for illustrative purposes, but if you're going to categorize fruit by color, why would you not also categorize meat the same way, i.e., white meat and red meat? Most likely you would not. So my point is it's probably not necessary to categorize fruit by color, either.
Instead, some attributes are better represented in other ways. In fact, in its simplest form, it could be recorded as a column in the 'food' table labeled 'color'. Or, if it's a very common attribute and you find yourself duplicating the value significantly, it could be split off to a separate table named 'color' and mapped to each food item from a third table. Of course, the more abstract approach would be to generalize the table as 'tags' and include each color as an individual tag that can then be mapped to any food item. Then you can map any number of tags (colors) to any number of food items, giving you a true many-to-many relationship and freeing up your category designations to be more generalized as well.
I know there's ongoing debate about whether tags are categories or categories are tags, etc., but this appears to be one instance in which they could be complimentary and create a more abstract and robust system that's easier to manage.
老线程，但我找到了这个问题的更好答案。
由于苹果可以有不同的颜色，所以你的结构是一个图，而不是一棵树。嵌套集合模型不是正确的结构。
由于您在评论中提到您正在使用 Mysql，因此更好的解决方案是使用 Open Query Graph 引擎（http: //openquery.com/graph/doc），这是一个 mysql 插件，可让您创建一个特殊的表，在其中放置关系（基本上是parentId 和 childId）。
神奇的是，您使用特殊的列锁存器查询该表，具体取决于查询中传递的值，这将告诉 OQGRAPH 引擎要执行哪个命令。有关详细信息，请参阅文档。
Old thread, but I found a better answer to this problem.
Since apple can have different color, your structure is a graph,not a tree. The nested set model is not the right structure for that.
Since you mention in a comment that you're using Mysql, a better solution is to use the Open Query Graph engine (http://openquery.com/graph/doc) which is a mysql plugin that lets you create a special table where you put the relationships, basically parentId and childId.
The magic is that you query this table with a special column latch depending of the value passed in the query will tell the OQGRAPH engine which command to execute. See the docs for details.