建模多对一元关系和1：M一元关系 [英] modeling many to many unary relationship and 1:M unary relationship

问题描述

我回到数据库设计中，我意识到我的知识方面存在巨大空白。

Im getting back into database design and i realize that I have huge gaps in my knowledge.

我有一个包含类别的表。每个类别可以有许多子类别，并且每个子类别可以属于许多超类别。

I have a table that contains categories. Each category can have many subcategories and each subcategory can belong to many super-categories.

我要创建一个文件夹，其类别名称将包含所有子类别文件夹。 （视觉对象，例如Windows文件夹）
所以我需要对子类别进行快速搜索。

I want to create a folder with a category name which will contain all the subcategories folders. (visual object like windows folders) So i need to preform quick searches of the subcategories.

我想知道在这种情况下使用1：M或M：N关系有什么好处？
以及如何实现每种设计？

I wonder what are the benefits of using 1:M or M:N relationship in this case? And how to implement each design?

我创建了一个ERD模型，它是1：M一元关系。 （该图还包含一个费用表，该表存储了所有费用值，但在这种情况下不相关）

I have create a ERD model which is a 1:M unary relationship. (the diagram also contains an expense table which stores all the expense values but is irrelevant in this case)

此设计正确吗？

多对多一元关系是否允许更快地搜索超级类别，并且默认情况下是最佳设计吗？

will many to many unary relationship allow for faster searches of super-categories and is the best design by default?

我希望得到一个包含ERD的答案

推荐答案

如果我对您的理解正确，则单个子类别最多可以具有一个（直接）超级类别，在这种情况下，您不需要单独的表。这样的东西就足够了：

If I understand you correctly, a single sub-category can have at most one (direct) super-category, in which case you don't need a separate table. Something like this should be enough:

很显然，您需要一个递归查询才能从各个级别获取子类别，但是如果您在PARENT_ID上添加了索引，它应该是相当有效的。

Obviously, you'd need a recursive query to get the sub-categories from all levels, but it should be fairly efficient provided you put an index on PARENT_ID.

朝相反的方向（并获得所有祖先）也将需要递归查询。由于这将需要在PK（自动索引）上进行搜索，因此它也应相当有效。

Going in the opposite direction (and getting all ancestors) would also require a recursive query. Since this would entail searching on PK (which is automatically indexed), this should be reasonably efficient as well.

有关更多概念和不同性能折衷的信息，请查看此幻灯片。

For some more ideas and different performance tradeoffs, take a look at this slide-show.

这篇关于建模多对一元关系和1：M一元关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！