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

问题描述

让我们看一个例子 - 书籍.一本书可以有 1..n 个作者.一个作者可以拥有 1..m 本书.代表一本书的所有作者的好方法是什么?

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?

我想出了一个创建 Books 表和 Authors 表的想法.Authors 表有一个主要的 AuthorID 键，即作者姓名.Books 表有一个主要的 Book ID 和关于这本书的元数据(书名、出版日期等).但是，需要有一种方法将书籍链接到作者，将作者链接到书籍.这就是问题所在.

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.

现在让我们向应用程序添加另一部分，即跟踪感兴趣的人的 Person 表.假设鲍勃是一个有趣的人.我不仅想说这三本书的作者都是鲍勃，而且这个有趣的鲍勃和作者鲍勃是同一个鲍勃.

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?

推荐答案

添加另一个名为 BookAuthors 的表，其中包含 BookID、AuthorID 和 NameUsed 列.NameUsed 的 NULL 值意味着改为从作者的表中提取它.这称为交集表.

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屋！