一对多与多对多关联设计和优点
我有一个非常基本的问题，由于某种奇怪的原因让我感到困惑。
我有两张表，一张是页面表，另一张是菜单。我想知道，使用另一个表进行映射有什么好处？下面是我正在考虑的两种实现：
我看到的唯一区别是我的搜索方式。 例如，在第一个实现中：
a）如果我想要特定菜单的所有页面，我将查询特定菜单名称的所有 page_id，并将它们与表 Pages 的 id 连接起来（这就是为什么我认为这个实现比较慢）。
b) 如果我想要特定页面的所有菜单，我将搜索具有特定页面的 page_id 的所有菜单。
第二种实现更加典型和直接（并且具有更多连接）。
我认为第二种实现是正确的，因为它更快（我猜，因为它只在索引 id 之间查询，而不是像我上面在 a）中提到的那样在菜单名称中搜索）。
还是有其他特殊原因？这两种设计在实现功能方面是否相同，或者第一种设计是否存在其他限制，而我总是应该选择第二种？
I have a pretty basic question that confuses me for some strange reason.
I have two tables, one is the table Pages and the other is the Menus. I am wondering, what is the benefits of using another table for mapping? Below is the two implementations that I am thinking:
The only difference I see is the way that I search.
For example in the first implementation:
a) if I want all the pages of a specific menu, I'll query all the page_ids of the specific menu name and I'll join them with the id of the table Pages (which is why I think this implementation is more slow).
b) if I want all the menus of a specific page I will search all the menus that have the page_id of the specific page.
In the second implementation is more typical and straightforward (and with more joins).
I think that the second implementation is the right one because it is faster (I guess, because it queries only between ids which is indexes instead of searching in menus names as I mentioned above in a)).
Or is there any other particular reason? Are these two designs identical in a matter of what they can accomplish or are there any other limitations in the first design and I always should choose the second one?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这两个选项不相同；后者是在多对多设计中使用的正确选择。使用连接表意味着您可以拥有许多页面（A、B、C）和许多菜单（1、2、3），并且可以将一组菜单与一组页面关联（产生 A1、A2、A3、B1） 、B2、B3、C1、C2、C3)。
第一个设计假设任何给定的菜单只能有一个与其关联的页面。菜单 1 只能与一页关联（是 A、B 还是 C？）。要将同一菜单与多个页面关联，您需要在菜单表中为每个关联页面保留一行。
The two options are NOT identical; the latter is the correct choice to use in a many-to-many design. Using a joining table means that you can have many pages (A, B, C) and many menus (1,2,3) and you can associate a set of menus with a set of pages (yielding A1, A2, A3, B1, B2, B3, C1, C2, C3).
The first design assumes that any given menu can only have 1 page associated with it. Menu 1 can only be associated with one page (will it be A, B, or C?). To associate the same menu with multiple pages, you'll need to have one row in your menu table for each associated page.
您的第一个实现是一对多，而不是多对多。您的第二个实现是带有有效负载的多对多（注意：它通常不需要 id）。
多对多意味着您可以将 n 个项目映射到 n 个其他项目。 1 对多意味着只有一个项目映射到 n 个项目。
如果您需要多对多，则只能在第二次实现中完成。如果您需要一对多，则采用第一种方式。
Your first implementation is a 1 to many, not a many to many. Your second implementation is a many to many with payload (note: it doesn't usually need an id).
Many to many means you can nave n items mapped to n other items. 1 to many means you only have one item mapping to n items.
If you need many to many, it can only be done in your second implementation. If you need 1 to many it is done the first way.
页面和菜单是多对多的关系。问题是，除了关系的简单事实之外，您还需要跟踪有关该关系的任何信息吗？
例如，如果您出版杂志，您可能有一种人与杂志之间的 M2M 关系——人可以订阅多本杂志，而杂志可以有多个订阅者。但是，您想要跟踪有关杂志和人物的交集信息 - 开始日期、到期日期等。使用交集表可以为您提供放置这些信息的位置。
我做这类事情已经有一段时间了，我可以想到相当多的实例，其中我有一个 M2M，其中交叉路口除了交叉路口的存在之外没有任何可记录的，因为系统最初实施时。然而，在几乎所有情况下，用户后来都要求进行增强，这需要有关关系的附加信息。
所以我总是使用交叉点。
Page and menu have a many-to-many relationship. The question is, is there any information about that relationship you need to track, other than the simple fact of the relationship?
If you published magazines, for example, you might have a person<-->magazine relationship that was M2M - persons could subscribe to multiple magazines and magazines could have multiple subscribers. But there's information about the intersection of magazine and person that you would want to track - start date, expiration date, etc. Using an intersection table gives you a place to put these.
I've been doing this sort of stuff for some time, and I can think of a fair number of instances where I had a M2M where the intersection had nothing to record other than the existence of the intersection, as the system was initially implemented. In nearly every case, though, the user later asked for an enhancement that would require additional information about the relationship.
So I use intersections, always.