database 数据库中的一对多关系-设计理念

one to many relationship in database - design concept

提问by user666

A one to many relationship between two tables should be implemented with two or three tables? For example should we have:

两个表之间的一对多关系应该用两个还是三个表来实现？例如，我们应该有：

author(id,otherAttributtes)
books(id,authorid,otherAttributes)

or

或者

 author(id,otherAttributtes)
    books(id,otherAttributes)
    authorConnectsBooks(authorid,booksid)

I like more the first approach but i have seen the second and in more complicated applications a lot of times. Is there any downside for the first method, or it's just personal which way to follow?

我更喜欢第一种方法，但我已经多次看到第二种方法和更复杂的应用程序。第一种方法有什么缺点吗，或者只是个人遵循哪种方式？

回答by JonH

The first example shows a one to many relationship, while the second shows a many to many relationship.

第一个示例显示一对多关系，而第二个示例显示多对多关系。

Example lets say we use the first example

示例假设我们使用第一个示例

Author
AuthorID

Book
BookID
AuthorID

How would you represent that both Jane and Jon wrote the book "Stackoverflow for fun"? In this relationship table you cannot, you have expressed that one author can write many books. So either Jane wrote it or Jon wrote it. If only one of them wrote the books you could use this relationship type. However, if you want to show that both wrote this book you need a many to many relationship.

你如何表示 Jane 和 Jon 都写了《Stackoverflow for fun》这本书？在这个关系表中你不能，你已经表示一个作者可以写很多书。所以要么是简写的，要么是乔恩写的。如果只有他们中的一个写了这些书，您可以使用这种关系类型。然而，如果你想证明这本书是双方都写的，你需要多对多的关系。

Now using this same analogy of Jane and Jon you can represent both authors to this one book using your second example - many to many relationship.

现在使用 Jane 和 Jon 的相同类比，您可以使用第二个示例代表这本书的两位作者 - 多对多关系。

---

---

让我们以 Stackoverflow 为例，以一对多关系开始，以多对多关系结束：

Authors
Joel
Jeff

Books
Stackoverflow Joel

Poor Jeff, he is not credited with stackoverflow from the above example...so we need to fix that:

可怜的杰夫，他没有从上面的例子中获得 stackoverflow 的功劳……所以我们需要解决这个问题：

Author
Joel
Jeff

Books
Stackoverflow

AuthorBooks
Stackoverflow Jeff
Stackoverflow Joel

Now everyone's happy...

现在大家都很开心...

回答by Cesar Daniel

A one-to-many relationship should be implemented with 2 tables.

一对多的关系应该用 2 个表来实现。

But the relationship you propose in your example (between Authors and Books) is not one-to-many, is many-to-many.

但是您在示例中提出的关系（作者和书籍之间）不是一对多的，而是多对多的。

"An Author can write many Books, and a Book can be written by one or more Authors."

“一个作者可以写很多本书，而一本书可以由一个或多个作者编写。”

And a many-to-many relationship should be implemented with 3 tables.

并且多对多关系应该用3个表来实现。

Have a good day.

祝你有美好的一天。

回答by Tony Hopkinson

one to many is two tables.

一对多是两张桌子。

the second one is many to many.

第二个是多对多。

Authors
1 Larry Niven
2 Jerry Pournelle

Books
1 Integral Trees
2 King David's Spaceship
3 The Mote in God's eye

AuthorsBooks
1 1
2 2
1 3
2 3

回答by Justin Cave

If the relationship is actually one to many, there is no need for a linking table (authorConnectsBooksin your example). In your example, however, you don't have a one-to-many relationship since one author can write many books and one book can be written by many authors. In your example, you actually have a many-to-many relationship. If you actually have a many-to-many relationship, then you do need a linking table (authorConnectsBooksin your example).

如果关系实际上是一对多，则不需要链接表（authorConnectsBooks在您的示例中）。但是，在您的示例中，您没有一对多的关系，因为一位作者可以写多本书，而一本书可以由多位作者编写。在你的例子中，你实际上有一个多对多的关系。如果您实际上有多对多关系，那么您确实需要一个链接表（authorConnectsBooks在您的示例中）。

回答by Nemin

As everyone stated out, the first one is a one to many relationship in which you dont need the extra table. Just two tables should work. But in the second case, since its a many to many realtionship, you'll need to add an extra table known as the Junction or the cross-reference table because most database management systems only support one-to-many relationships, it is necessary to implement such relationships manually via a third junction table. The junction table's primary key is normally formed using the primary keys of the tables it connects. Here is a wiki page that explains the exact same example that you asked:

正如每个人所说，第一个是一对多关系，您不需要额外的表。只有两个表应该工作。但在第二种情况下，由于它是多对多的关系，您需要添加一个额外的表，称为连接或交叉引用表，因为大多数数据库管理系统只支持一对多关系，这是必要的通过第三个连接表手动实现这种关系。联结表的主键通常是使用它连接的表的主键形成的。这是一个 wiki 页面，它解释了您询问的完全相同的示例：

LINK

关联