database 数据库设计中的多对多关系
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1273715/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
many-to-many relationship in database design
提问by ak3nat0n
I currently have a database with two tables called Articles and Tags . In order to allow articles to be in multiple categories i have a many to many relationship. Is it a mistake to have such a design in terms of performance? or should i remove the relationship between these two table and add a third table as a bridge (articlesTags)?
我目前有一个包含两个表的数据库，称为 Articles 和 Tags 。为了让文章属于多个类别，我有一个多对多的关系。在性能方面有这样的设计是错误的吗？或者我应该删除这两个表之间的关系并添加第三个表作为桥梁（articlesTags）？
回答by Donut
There's nothing inherently wrong with having a many-to-many relationship, you'll just need to create a Junction Table(which is what it sounds like you're referring to with articlesTags) to facilitate that relationship.
拥有多对多关系并没有本质上的错误，您只需要创建一个连接表（这听起来像是您所指的articlesTags）来促进这种关系。
回答by James
You're seeing the difference between a conceptual database design (the N:N relationship) and it's physical realisation. No matter how you model your N:N relationship, you'll need the aforementioned Junction Table to make it work.
您会看到概念数据库设计（N:N 关系）与其物理实现之间的区别。无论您如何建模您的 N:N 关系，您都需要上述连接表才能使其工作。
There's nothing wrong with modeling a real world relationship as close to the real world as you can as a general statement. Clarity is king.
将现实世界的关系建模为尽可能接近现实世界的一般陈述并没有错。清晰才是王道。
When it comes to any performance question in any system the answer usually boils down to "it depends".
当谈到任何系统中的任何性能问题时，答案通常归结为“视情况而定”。
If your performance problem is related to WRITES then a highly NORMALISED structure is best and you'll want that Junction table. You'll end up writing a lot less data and that can speed things up substantially (though you may burn that advantage by having to do lookups before you create the inserts). Reading from the individual normalised tables can be very fast too.
如果您的性能问题与 WRITES 相关，那么高度规范化的结构是最好的，您将需要该连接表。您最终会写入更少的数据，这可以大大加快速度（尽管您可能会因为必须在创建插入之前进行查找而消耗这种优势）。从单个规范化表中读取也可以非常快。
If your problem is related to analytical READS then a DENORMALISED structure is best. Joins can be very performance intensive if the tables are large and the indices spread out. You'll sacrifice a lot of space to gain a lot of time.
如果您的问题与分析 READS 相关，那么 DENORMALISED 结构是最好的。如果表很大并且索引分散，连接可能会非常消耗性能。你会牺牲很多空间来获得很多时间。
In general, you want to look at the specifics of your situation and weigh the pros and cons of each approach before deciding on a solution. Personally, I've always found it better to focus on Clarity in the initial stages and refactor for performance if I discover a problem later.
一般来说，在决定解决方案之前，您需要查看您的具体情况并权衡每种方法的利弊。就我个人而言，我总是发现在初始阶段关注 Clarity 并在以后发现问题时重构性能更好。
回答by Lliane
A many-to-many relationship exists in a relationnal model, it's just an abstraction of the mind. When you'll implement it there will be a articles_to_tags table where you'll have :
关系模型中存在多对多关系，它只是思想的抽象。当您实现它时，将会有一个articles_to_tags 表，您将在其中拥有：
fk_article(INTEGER) fk_tag(INTEGER)
fk_article(INTEGER) fk_tag(INTEGER)
回答by Lakshman Prasad
There is no problem in using Many to Many relationships. It is often required.
使用多对多关系没有问题。经常需要它。
And yes, it is not possible to create a many to many relation, without using a third table.
是的，如果不使用第三个表，就不可能创建多对多关系。
回答by Randy
There's no problem with having a many-to-many relationship if that's what the data require, but you'll want a 3rd table to represent it.
如果这是数据所需要的，那么拥有多对多关系没有问题，但是您需要第三个表来表示它。
