为什么没有多对多关系？ [英] Why no many-to-many relationships?

问题描述

我第一次了解数据库和SQL。在我正在阅读的文本（Oracle 11g：SQL by Joan Casteel）中，它说多对多关系不能存在于关系数据库中。我理解我们要避免它们，我知道如何创建一个桥接实体来消除它们，但我想完全理解这个语句不能存在。

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."

实际上不可能有多对多关系表示？

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. :-)

谢谢！

推荐答案

考虑一个简单的关系，如作者和图书之间的关系。作者可以写很多书。一本书可能有很多作者。现在，没有一个桥表来解决多对多关系，替代方案是什么？您必须在Books表中添加多个Author_ID列，每个作者一个。但你添加了多少？ 2？ 3？ 10？然而，许多你选择，你可能会得到很多稀疏的行，其中许多Author_ID值为NULL，有一个很好的机会，你会遇到一个case，你需要只是一个。所以，你要么不断地修改模式，以试图适应，或者你强加一些人为的限制（没有书可以有超过3个作者）来强制适合。

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.

这篇关于为什么没有多对多关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！