需要关联表的多对多关系
在许多数据库设计教程/文章中，他们总是提出这样一个事实：如果两个表共享多对多关系，则应该创建第三个表作为关联表来链接这两个表。
但是，他们从未提供我们应该这样做的原因为什么。我很想知道原因以及如果您将两个表保留为可能发生的问题的示例它没有关联表。
In many database design tutorials/articles, they always bring up the fact that if two tables share a many-to-many relationship, then a third table should be created to act as an associate table to link the two.
However, they never provide the reason WHY we should do it that way. I'm curious to know why and examples of problems that could occur if you just kept the two tables as it is without the associate table.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
如果您不创建第三个表，则根本没有地方可以存储关系。
对于一对一或一对多关系，您可以将关系存储在其中一个表中。对于多对多关系，您必须单独存储这些关系。 （好吧，理论上您可以将其存储为两个表中以逗号分隔的身份列表，但这对于使用和维护来说将是一场噩梦。）
If you don't create a third table, there is simply nowhere to store the relations.
With a one-to-one or one-to-many relation, you can store the relation in one of the tables. With a many-to-many relation you have to store the relations separately. (Well, theoretically you could store it as a comma separated list of identities in both tables, but that would be a nightmare to use and to maintain.)
在关系数据库中，所有关系仅以一种方式表示：关系（关系对应于 SQL 中的表）。具有两个属性的关系（例如 R{A,B}）表示 A 和 B 之间的二元关系。例如，该关系可以是一对多或多对多。
如果 R{A,B} 表示的关系是多对多，则意味着 A 或 B 都不是候选键（因为如果其中任何一个是唯一的，则显然只允许该属性的每个值有一个元组）。这意味着第三范式的原则要求依赖于 A 或 B 的任何属性都可以放入其他表中。原因是非键依赖性（依赖于 A 或 B 的属性）是一种冗余形式，可能会导致异常和不正确的结果。
因此，“多对多关系”的表示方式与其他关系并没有什么不同。只是规范化通常会导致表具有复合键且没有其他非键属性的常见模式。有些人喜欢将该模式称为关联表 - 尽管我个人认为该术语不是很有帮助。
In relational databases all relationships are represented in only one way: as relations (relations correspond to tables in SQL). A relation with two attributes, such as R{A,B}, represents a binary relationship between A and B. That relationship could be one-to-many or many-to-many for example.
If the relationship represented by R{A,B} is many-to-many that implies that neither A or B are candidate keys (because if either was unique then obviously only ONE tuple for each value of that attribute would be permitted). That means that the principle of Third Normal Form requires any attributes dependent on A or B to go in other tables. The reason for this is that non-key dependencies (attributes dependent on A or B) are a form of redundancy and can cause anomalies and incorrect results.
So it's not that "many-to-many relationships" are represented any differently to other relationships. It's just that normalization often leads to a common pattern with tables with compound keys and no other non-key attributes. Some people like to call that pattern an Association table - although personally I don't find that terminology very helpful.
让我们通过一个例子来理解它。如果您有两张表，一张用于苹果，另一张用于人员，并且它们具有多对多关系；意思是一个人可以有多个苹果，一个苹果可以多个人分享吃。
因此，如果我们在 person 表中的 apple 表中添加一个外键，那么这就给出了一个苹果可以被多个人吃的关系，但我们没有指定相反的关系。请参阅此表
同样，如果我们在苹果表中的人员表中添加一个外键，那么这会给出一个人可以吃多个苹果的关系，但不会给出其他关系。参见此表
所以这里是关联表。关联表用于两个对象之间的多对多关系。它们至少由 2 个外键组成，每个外键引用两个对象之一，并且关联表中的主键至少由 2 个外键组成。请参阅此表
参考：
https://openclassrooms. com/en/courses/2071486-retrieve-data-using-sql/5758019-create-an-association-table
Let's understand it with an example. If you have two tables one for apples and other for persons and they have a many to many relationship; meaning one person can have multiple apple and one apple can be eaten by multiple person by sharing.
So if we add a foreign key to apple table in the person table then this gives the relationship that one apple can be eaten by multiple person but we have not specified the relationship for the other way around. See this table
Similarly, if we add a foreign key to person table in the apple table then this gives the relationship that one person can eat multiple apples but not the other relationship along with it.See this table
So here comes the association table. Association table are used for many to many relationship between two objects. They consists of at least 2 foreign keys, each of which reference one of the two objects and the primary key in an Association table is made up of at least the 2 foreign keys. See this table
Reference:
https://openclassrooms.com/en/courses/2071486-retrieve-data-using-sql/5758019-create-an-association-table
您无法使用关系数据库以任何其他方式建立多对多关系。即，如果您有一个名为“person”的表，则无法创建“friends”列并期望将许多朋友的用户 ID 放入其中。您必须创建一个单独的表来保存关系本身。
You can't make many to many relations any other way using relational databases. Ie, if you have a table called "person", you can't create a column "friends" and expect to put many friends' user ids in there. You have to make a separate table to hold the relation itself.
维基百科也对此进行了描述。看看： http://en.wikipedia.org/wiki/ Many-to-many_(data_model)
如果您仍然不相信多对多确实需要第三个表，只需尝试仅使用两个表的作者/书籍示例（如维基百科文章中所述）标准化表。
Wikipedia describes it too. Just take a look: http://en.wikipedia.org/wiki/Many-to-many_(data_model)
If you still don't believe that many-to-many do realy need a third table, just try the Authors/Books examble (as described in the wikipedia article) with only two normalized tables.
如果 DB-Server 可以为您创建第三个表，那就不灵活了。如果有这样的解决方案，您对
更改关系行为
在assoc中存储其他关联信息不会有太大影响。表
性能增强
存储增强
It would not flexible if DB-Server could create 3rd table for you. If there will be a solution like that, you wouldn't have much influence to
change relation behavour
store other association information in assoc. table
performance enhancements
storage enhancements