如何在关系数据库中处理m..n关系？ [英] How do you deal with m..n relationships in a relational database?

问题描述

让我们来看一个例子 - 书。一本书可以有1..n作者。作者可以有1..m的书。什么是表示一本书的所有作者的好方法？

我想出了创建一个Books表和一个Authors表。 Authors表具有作者姓名的主AuthorID键。图书表具有主图书ID和有关图书的元数据（标题，出版日期等）。然而，需要有一种方法将图书与作者和作者联系起来。这是问题所在。

我们假设我们有三本书。然而，在一本书上，他写了鲍勃，博士。另一个他写作博士鲍勃，第三个他写作博士罗伯特。我想要能够识别这样一个事实，即这些作者实际上是同一个人，但以不同的名字。我也想要区别鲍勃和写不同书籍的另一个鲍勃。

现在让我们再添加一个部分到一个应用程序，一个Person表跟踪有趣的人。让我们说，鲍勃是一个有趣的人。我不仅要说所有三本书的作者都是鲍勃，而且这个有趣的鲍勃与作者鲍勃一样是鲍勃。

所以，

解决方案

添加另一个名为BookAuthors的表其中包含BookID，AuthorID和NameUsed的列。 NameUsed的NULL值将意味着从作者表中拉。这称为交叉表。

Let's look at an example - books. A book can have 1..n authors. An author can have 1..m books. What is a good way to represent all of the authors of a book?

I came up with an idea to create a Books table and an Authors table. The Authors table has a primary AuthorID key the author's name. The Books table has a primary Book ID and metadata about the book (title, publication date, so on). However, there needs to be a way to link the books to authors and authors to books. And this is where the problem is.

Let's say we have three books by Bob. However, on one book, he wrote it as Bob, PhD. Another he wrote as Dr. Bob, and a third he wrote as Dr. Robert. I want to be able to identify the fact that these authors are, in reality, the same person but credited under different names. I also want to distinguish Bob from another Bob who wrote different books.

Now let's also add in another part to an application, a Person table that keeps track of interesting people. And let's say that Bob is an interesting person. I want to not only say that the author of all three books is Bob, but that this interesting Bob is the same Bob as the author Bob.

So what strategies exist for such potentially complicated mapping, while ensuring that the book authors are identified by the name on the cover?

解决方案

Add another table called BookAuthors with columns for BookID, AuthorID, and NameUsed. A NULL value for NameUsed would mean to pull it from the Author's table instead. This is called an Intersection table.

这篇关于如何在关系数据库中处理m..n关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！