SQL 为什么没有多对多关系？

Why no many-to-many relationships?

提问by The111

I am learning about databases and SQL for the first time. In the text I'm reading (Oracle 11g: SQL by Joan Casteel), it says that "many-to-many relationships can't exist in a relational database." I understand that we are to avoid them, and I understand how to create a bridging entity to eliminate them, but I am trying to fully understand the statement "can't exist."

我是第一次学习数据库和 SQL。在我正在阅读的文本（Oracle 11g：SQL by Joan Casteel）中，它说“关系数据库中不能存在多对多关系”。我明白我们要避免它们，我明白如何创建一个桥接实体来消除它们，但我试图完全理解“不可能存在”这句话。

Is it actually physically impossible to have a many-to-many relationship represented?

代表多对多关系实际上在物理上是不可能的吗？

Or is it just very inefficient since it leads to a lot of data duplication?

或者它只是非常低效，因为它会导致大量数据重复？

It seems to me to be the latter case, and the bridging entity minimizes the duplicated data. But maybe I'm missing something? I haven't found a concrete reason (or better yet an example) that explains why to avoid the many-to-many relationship, either in the text or anywhere else I've searched. I've been searching all day and only finding the same information repeated: "don't do it, and use a bridging entity instead." But I like to ask why. :-)

在我看来是后一种情况，桥接实体最大限度地减少了重复数据。但也许我错过了什么？我还没有找到一个具体的原因（或者更好的例子）来解释为什么要避免多对多关系，无论是在文本中还是在我搜索过的其他任何地方。我一整天都在搜索，只发现重复的相同信息：“不要这样做，而是使用桥接实体。” 但我喜欢问为什么。:-)

Thanks!

谢谢！

回答by Joe Stefanelli

Think about a simple relationship like the one between Authors and Books. An author can write many books. A book could have many authors. Now, without a bridge table to resolve the many-to-many relationship, what would the alternative be? You'd have to add multiple Author_ID columns to the Books table, one for each author. But how many do you add? 2? 3? 10? However many you choose, you'll probably end up with a lot of sparse rows where many of the Author_ID values are NULL and there's a good chance that you'll run across a case where you need "just one more." So then you're either constantly modifying the schema to try to accommodate or you're imposing some artificial restriction ("no book can have more than 3 authors") to force things to fit.

考虑一种简单的关系，例如 Authors 和 Books 之间的关系。一个作者可以写很多书。一本书可以有很多作者。现在，如果没有桥接表来解决多对多关系，那么替代方案是什么？您必须向 Books 表添加多个 Author_ID 列，每个作者一个。但是你加了多少？2？3？10？无论您选择多少行，您最终都可能会得到很多 Author_ID 值为 NULL 的稀疏行，并且很有可能会遇到您需要“多一个”的情况。因此，您要么不断修改架构以尝试适应，要么强加一些人为限制（“没有一本书的作者不能超过 3 位”）以强制适应。

回答by Mike_OBrien

A true many-to-many relationship involving two tables is impossible to create in a relational database. I believe that is what they refer to when they say that it can't exist. In order to implement a many to many you need an intermediary table with basically 3 fields, an ID, an id attached to the first table and an id atached to the second table.

在关系数据库中不可能创建真正涉及两个表的多对多关系。我相信当他们说它不可能存在时，这就是他们所指的。为了实现多对多，您需要一个基本上包含 3 个字段的中间表，一个 ID，一个附加到第一个表的 id 和一个附加到第二个表的 id。

The reason for not wanting many-to-many relationships, is like you said they are incredibly inefficient and managing all the records tied to each side of the relationship can be tough, for instance if you delete a record on one side what happens to the records in the relational table and the table on the other side? Cascading deletes is a slippery slope, at least in my opinion.

不想要多对多关系的原因就像你说的它们效率低得令人难以置信，并且管理与关系每一侧相关的所有记录可能很困难，例如，如果您删除一侧的记录会发生什么关系表中的记录和另一侧的表？级联删除是一个滑坡，至少在我看来。

回答by JDPeckham

Normally (pun intended) you would use a link table to establish many-to-many

通常（双关语）您会使用链接表来建立多对多

Like described by Joe Stefanelli, let's say you had Authors and Books

就像 Joe Stefanelli 所描述的那样，假设您有作者和书籍

SELECT * from Author
SELECT * from Books

you would create a JOINtable called AuthorBooks

您将创建一个JOIN名为 AuthorBooks的表

Then,

然后，

SELECT * from Author a JOIN AuthorBooks ab on a.AuthorId = ab.AuthorId JOIN Books b on ab.BookId = b.BookId

hope that helps.

希望有帮助。

回答by Thomas

it says that "many-to-many relationships can't exist in a relational database."

它说“多对多关系不能存在于关系数据库中”。

I suspect the author is just being controversial. Technically, in the SQL language, there is no means to explicitly declare a M-M relationship. It is an emergent result of declaring multiple 1-M relations to the table. However, it is a common approach to achieve the result of a M-M relationship and it is absolutely used frequently in databases designed on relational database management systems.

我怀疑作者只是有争议。从技术上讲，在 SQL 语言中，没有办法显式声明 MM 关系。它是向表声明多个 1-M 关系的紧急结果。但是，这是实现MM关系结果的常用方法，并且在关系数据库管理系统上设计的数据库中绝对经常使用。

I haven't found a concrete reason (or better yet an example) that explains why to avoid the many-to-many relationship,

我还没有找到一个具体的原因（或者更好的例子）来解释为什么要避免多对多关系，

They should be used where they are appropriate to be used would be a more accurate way of saying this. There are times, such as the books and authors example given by Joe Stafanelli, where any other solution would be inefficient and introduce other data integrity problems. However, M-M relationships are more complicated to use. They add more work on the part of the GUI designer. Thus, they should only be used where it makes sense to use them. If you are highly confident that one entity should never be associated with more than one of some other entity, then by all means restrict it to a 1-M. For example, if you were tracking the status of a shipment, each shipment can have only a single status at any given time. It would over complicate the design and not make logical sense to allow a shipment to have multiple statuses.

它们应该用在适合使用的地方，这是一种更准确的说法。有时，例如 Joe Stafanelli 给出的书籍和作者示例，任何其他解决方案都将效率低下并引入其他数据完整性问题。但是，MM 关系使用起来更复杂。它们增加了 GUI 设计人员的更多工作。因此，它们应该只在有意义的地方使用。如果您非常确信一个实体不应与多个其他实体相关联，那么务必将其限制为 1-M。例如，如果您要跟踪货件的状态，则每个货件在任何给定时间只能有一个状态。允许货物具有多种状态会使设计过于复杂并且没有逻辑意义。

回答by sequel

It is correct. The Many to Many relationship is broken down into several One to Many relationships. So essentially, NO many to many relationship exists!

它是正确的。多对多关系被分解为几个一对多关系。所以本质上，不存在多对多的关系！

回答by Jeremy Holovacs

Of course they can (and do) exist. That sounds to me like a soapbox statement. They are required for a great many business applications.

当然，它们可以（并且确实）存在。对我来说，这听起来像是一个肥皂盒声明。许多业务应用程序都需要它们。

Done properly, they are not inefficient and do not have duplicate data either.

如果做得好，它们的效率并不低，也没有重复的数据。

Take a look at FaceBook. How many many-to-many relationships exist between friends and friends of friends? That is a well-defined business need.

看看脸书。朋友和朋友的朋友之间存在多少多对多的关系？这是一个明确定义的业务需求。

The statement that "many-to-many relationships can't exist in a relational database." is patently false.

声明“关系数据库中不能存在多对多关系”。显然是假的。

回答by eaj

Many-to-many relationships are in fact very useful, and also common. For example, consider a contact management system which allows you to put people in groups. One person can be in many groups, and each group can have many members.

多对多关系实际上非常有用，而且也很常见。例如，考虑一个联系人管理系统，它允许您将人员分组。一个人可以在多个组中，每个组可以有多个成员。

Representation of these relations requires an extra table--perhaps that's what your book is really saying? In the example I just gave, you'd have a Person table (id, name, address etc) and a Group table (id, group name, etc). Neither contains information about who's in which group; to do that you have a third table (call it PersonGroup) in which each record contains a Person ID and a Group ID--that record represents the relation between the person and the group.

这些关系的表示需要一个额外的表格——也许这就是你的书真正要说的？在我刚刚给出的示例中，您将有一个 Person 表（id​​、名称、地址等）和一个 Group 表（id​​、组名称等）。两者都不包含有关谁在哪个组中的信息；为此，您有第三个表（称为 PersonGroup），其中每条记录都包含一个人员 ID 和一个组 ID——该记录表示人员和组之间的关系。

Need to find the members of a group? Your query might look like this (for the group with ID=1):

需要查找组的成员？您的查询可能如下所示（对于 ID=1 的组）：

SELECT Person.firstName, Person.lastName 
FROM Person JOIN PersonGroup JOIN Group 
ON (PersonGroup.GroupID = 1 AND PersonGroup.PersonID = Person.ID);

回答by Durgesh Sindhi

Well, of course M-M relationship does exist in relational databases and they also have capability of handling at some level through bridging tables, however as the degree of M-M relationship increases it also increases complexity which results in slow R-W cycles and latency. It is recommended to avoid such complex M-M relationships in a Relational Database. Graph Databases are the best alternative and good at handling Many to Many relationship between objects and that's why social networking sites uses Graph databases for handling M-M relationship between User and Friends, Users and Events etc.

嗯，当然 MM 关系确实存在于关系数据库中，它们也有通过桥接表在一定程度上处理的能力，但是随着 MM 关系程度的增加，它也会增加复杂性，从而导致慢 RW 周期和延迟。建议在关系数据库中避免这种复杂的 MM 关系。图数据库是最好的选择，擅长处理对象之间的多对多关系，这就是为什么社交网站使用图数据库来处理用户和朋友、用户和事件等之间的 MM 关系。

回答by Harrykesh Ramma

Let's invent a fictional relationship (many to many relationship) between books and sales table. Suppose you are buying books and for each book you buy needs to generate an invoice number for that book. Suppose also that the invoice number for a book can represent multiple sales to the same customer (not in reality but let's assume). We have a many to many relationship between books and sales entities. Now if that's the case, how can we get information about only 1 book given that we have purchased 3 books since all books would in theory have the same invoice number? That introduces the main problem of using a many to many relationship I guess. Now if we add a bridging entity between Books and sales such that each book sold have only 1 invoice number, no matter how many books are purchases we can still correctly identify each books.

让我们在书籍和销售表之间创建一个虚构的关系（多对多关系）。假设您正在购买书籍，并且您购买的每本书都需要为该书籍生成发票编号。还假设一本书的发票编号可以代表同一客户的多次销售（实际上并非如此，但让我们假设）。我们在书籍和销售实体之间存在多对多关系。现在如果是这种情况，鉴于我们已经购买了 3 本书，因为理论上所有书籍都具有相同的发票编号，我们如何才能获得仅 1 本书的信息？我猜这引入了使用多对多关系的主要问题。现在，如果我们在 Books 和 sales 之间添加一个桥接实体，使得每本书销售只有 1 个发票编号，无论购买了多少本书，我们仍然可以正确识别每本书。

回答by PureSilence

In a many-to-many relationship there is obvious redundancy as well as insert, update and delete anomaly which should be eliminated by converting it to 2 one-to-many relationship via a bridge table.

在多对多关系中，存在明显的冗余以及插入、更新和删除异常，应通过桥表将其转换为 2 一对多关系来消除这些异常。