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!
谢谢!
8 个解决方案
#1
38
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.
想想一个简单的关系，比如作者和书之间的关系。一个作家可以写很多书。一本书可以有许多作者。现在，如果没有一个桥接表来解决多对多关系，还有什么可选的呢?您必须向Books表添加多个Author_ID列，每个作者一个。但是要加多少呢?2呢?3 ?10 ?不管您选择多少，您最终可能会得到许多稀疏的行，其中许多Author_ID值为NULL，并且很有可能在您需要“仅仅一个”的情况下运行。因此，要么不断修改模式以适应，要么施加一些人为的限制(“任何一本书都不能有超过3个作者”)，以迫使内容符合要求。
#2
4
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.
在关系数据库中不可能创建涉及两个表的真正多对多关系。我相信这就是他们所说的不可能存在。为了实现许多，您需要一个中间表，其中包含三个字段，一个ID，一个连接到第一个表的ID和一个atache连接到第二个表的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.
不希望多对多关系的原因,就像你说的他们是令人难以置信的效率低下和管理所有的记录与每一方的关系可以是艰难的,例如,如果你删除一个记录一侧发生关系表和表中的记录另一边?级联删除是一种滑坡，至少在我看来是这样。
#3
3
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 JOIN table called AuthorBooks
您将创建一个名为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.
希望有帮助。
#4
2
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语言中，没有方法显式声明M-M关系。它是向表声明多个1-M关系的紧急结果。然而，它是实现M-M关系结果的一种常见方法，在关系数据库管理系统上设计的数据库中绝对经常使用。
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.
它们应该在合适的地方使用，这样说会更准确。有时，如乔·斯塔法内利给出的书籍和作者示例，任何其他解决方案都是低效的，并会引入其他数据完整性问题。然而，M-M关系使用起来更加复杂。他们在GUI设计器部分添加了更多的工作。因此，它们只能在有意义的地方使用它们。如果您非常确信一个实体不应该与其他某个实体相关联，那么务必将其限制为1-M。例如，如果您正在跟踪货物的状态，那么每批货物在任何时候都只能有一个状态。它会使设计复杂化，并没有合理的理由允许货物有多个状态。
#5
1
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.
看看FaceBook吧。朋友和朋友的朋友之间有多少多对多的关系?这是一种良好定义的业务需求。
The statement that "many-to-many relationships can't exist in a relational database." is patently false.
“多对多关系不能存在于关系数据库中”的语句显然是错误的。
#6
1
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)，其中每个记录包含一个Person ID和一个Group ID——该记录表示Person和这个组之间的关系。
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);
#7
1
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!
它是正确的。许多到许多的关系被分解成许多到许多的关系。所以从本质上说，没有多少到多少的关系存在!
#8
-1
M:N relationships should not exist in database design. They are extremely inefficient and do not make for functional databases. Two tables (entities) with a many-to-many relationship (aircraft, airport; teacher, student) cannot both be children of each other, there would be no where to put foreign keys without an intersecting table. aircraft-> flight <- airport; teacher <- class -> student.
M:在数据库设计中不应该存在N种关系。它们的效率极低，不适合使用功能性数据库。具有多对多关系的两个表(实体)(飞机、机场;老师，学生)不能都是对方的孩子，没有交叉的桌子就没有放外文钥匙的地方。飞机飞行- > < -机场;教师 <班级-> 学生。
An intersection table provides a place for an entity that is dependent on two other tables, for example, a grade needs both a class and a student, a flight needs both an aircraft and an airport. Many-to-many relationships conceal data. Intersection tables reveal this data and create one-to-many relationships that can be more easily understood and worked with. So, the question arises, what table should the flight be in--aircraft or airport. Neither, they should be foreign keys in the intersection table, Flight.
交叉表为一个实体提供了一个位置，该实体依赖于另外两个表，例如，一个年级需要一个班和一个学生，一个航班需要一个飞机和一个机场。多对多关系隐藏数据。交叉表显示了这些数据，并创建了一对多的关系，可以更容易地理解和使用它们。因此，问题出现了，飞机或机场应该在哪个表格上。它们都不应该是交集表中的外键。
#1
38
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.
想想一个简单的关系，比如作者和书之间的关系。一个作家可以写很多书。一本书可以有许多作者。现在，如果没有一个桥接表来解决多对多关系，还有什么可选的呢?您必须向Books表添加多个Author_ID列，每个作者一个。但是要加多少呢?2呢?3 ?10 ?不管您选择多少，您最终可能会得到许多稀疏的行，其中许多Author_ID值为NULL，并且很有可能在您需要“仅仅一个”的情况下运行。因此，要么不断修改模式以适应，要么施加一些人为的限制(“任何一本书都不能有超过3个作者”)，以迫使内容符合要求。
#2
4
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.
在关系数据库中不可能创建涉及两个表的真正多对多关系。我相信这就是他们所说的不可能存在。为了实现许多，您需要一个中间表，其中包含三个字段，一个ID，一个连接到第一个表的ID和一个atache连接到第二个表的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.
不希望多对多关系的原因,就像你说的他们是令人难以置信的效率低下和管理所有的记录与每一方的关系可以是艰难的,例如,如果你删除一个记录一侧发生关系表和表中的记录另一边?级联删除是一种滑坡，至少在我看来是这样。
#3
3
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 JOIN table called AuthorBooks
您将创建一个名为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.
希望有帮助。
#4
2
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语言中，没有方法显式声明M-M关系。它是向表声明多个1-M关系的紧急结果。然而，它是实现M-M关系结果的一种常见方法，在关系数据库管理系统上设计的数据库中绝对经常使用。
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.
它们应该在合适的地方使用，这样说会更准确。有时，如乔·斯塔法内利给出的书籍和作者示例，任何其他解决方案都是低效的，并会引入其他数据完整性问题。然而，M-M关系使用起来更加复杂。他们在GUI设计器部分添加了更多的工作。因此，它们只能在有意义的地方使用它们。如果您非常确信一个实体不应该与其他某个实体相关联，那么务必将其限制为1-M。例如，如果您正在跟踪货物的状态，那么每批货物在任何时候都只能有一个状态。它会使设计复杂化，并没有合理的理由允许货物有多个状态。
#5
1
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.
看看FaceBook吧。朋友和朋友的朋友之间有多少多对多的关系?这是一种良好定义的业务需求。
The statement that "many-to-many relationships can't exist in a relational database." is patently false.
“多对多关系不能存在于关系数据库中”的语句显然是错误的。
#6
1
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)，其中每个记录包含一个Person ID和一个Group ID——该记录表示Person和这个组之间的关系。
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);
#7
1
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!
它是正确的。许多到许多的关系被分解成许多到许多的关系。所以从本质上说，没有多少到多少的关系存在!
#8
-1
M:N relationships should not exist in database design. They are extremely inefficient and do not make for functional databases. Two tables (entities) with a many-to-many relationship (aircraft, airport; teacher, student) cannot both be children of each other, there would be no where to put foreign keys without an intersecting table. aircraft-> flight <- airport; teacher <- class -> student.
M:在数据库设计中不应该存在N种关系。它们的效率极低，不适合使用功能性数据库。具有多对多关系的两个表(实体)(飞机、机场;老师，学生)不能都是对方的孩子，没有交叉的桌子就没有放外文钥匙的地方。飞机飞行- > < -机场;教师 <班级-> 学生。
An intersection table provides a place for an entity that is dependent on two other tables, for example, a grade needs both a class and a student, a flight needs both an aircraft and an airport. Many-to-many relationships conceal data. Intersection tables reveal this data and create one-to-many relationships that can be more easily understood and worked with. So, the question arises, what table should the flight be in--aircraft or airport. Neither, they should be foreign keys in the intersection table, Flight.
交叉表为一个实体提供了一个位置，该实体依赖于另外两个表，例如，一个年级需要一个班和一个学生，一个航班需要一个飞机和一个机场。多对多关系隐藏数据。交叉表显示了这些数据，并创建了一对多的关系，可以更容易地理解和使用它们。因此，问题出现了，飞机或机场应该在哪个表格上。它们都不应该是交集表中的外键。