SQL 你如何处理关系数据库中的 m..n 关系？

How do you deal with m..n relationships in a relational database?

提问by Thomas Owens

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?

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

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.

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

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.

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

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

那么，对于这种潜在的复杂映射，存在哪些策略，同时确保通过封面上的姓名识别书籍作者？

回答by Joel Coehoorn

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.

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

回答by tom.dietrich

You'd need three tables -

你需要三张桌子——

1. Book
2. Author
3. BookAuthors

1. 书
2. 作者
3. 图书作者

Book would contain book id, book title, and all the other information you need to collect about the book.

Book 将包含书名、书名以及您需要收集的有关该书的所有其他信息。

Author would contain author ID as well as other information such as First name, last name that you need to collect about any given author.

作者将包含作者 ID 以及其他信息，例如您需要收集的有关任何给定作者的名字、姓氏。

BookAuthors would be a many-to-many join, containing BookID, AuthorID, and NameUsed. This would allow the book to have either zero or many authors, for an author to have either zero or many books, and for information about that relationship to be captured. You could also, for example, have a column on the BookAuthor table that described the author's relationship to the book ("Edited By", "Fore word by").

BookAuthors 将是多对多连接，包含 BookID、AuthorID 和 NameUsed。这将允许一本书有零个或多个作者，一个作者有零个或多个书，并允许捕获有关该关系的信息。例如，您还可以在 BookAuthor 表上有一列描述作者与图书的关系（“编辑者”、“前言者”）。

回答by Paul Tomblin

I think you're pretty much there. You need a Books table, an Authors table, and then a "authors_of_books" table with the primary key of the book, the primary key of the author, and a "cited as" text field showing how that particular author was cited on that book.

我认为你几乎在那里。您需要一个 Books 表、一个 Authors 表，然后是一个“authors_of_books”表，其中包含该书的主键、作者的主键和一个“引用为”文本字段，显示该特定作者是如何在该书中被引用的.

回答by theraccoonbear

This sounds like a many-to-many relationship, not a 1-to-many. I think you'll want to use a table between those two that allows you to define 1-to-many on either side of that. Check this out...

这听起来像是多对多的关系，而不是一对多的关系。我认为您会希望在这两者之间使用一个表格，以便您在其两侧定义一对多。看一下这个...

http://www.tekstenuitleg.net/en/articles/database_design_tutorial/8

http://www.tekstenuitleg.net/en/articles/database_design_tutorial/8

回答by Craig

Given that Dr. Bob and Dr. Robert and Bob, PhD are all the same person, they would link to the same row in the authors table.

鉴于 Dr. Bob 和 Dr. Robert 以及 Bob, PhD 都是同一个人，他们将链接到作者表中的同一行。

However, I think what you need is a person table that authors links to. You could also link your interesting person table to it. That way Author Bob and Author Robert as well as Interesting Bob link to Person Bob. Hope that makes sense.

但是，我认为您需要的是作者链接到的人员表。您还可以将您的有趣人物表链接到它。这样作者鲍勃和作者罗伯特以及有趣的鲍勃链接到个人鲍勃。希望这是有道理的。

回答by Dana

The first thing that comes to mind is to have a link table, perhaps called AuthorOf to link books with their authors.

想到的第一件事是有一个链接表，可能称为 AuthorOf 以将书籍与其作者联系起来。

The columns would be AuthorID, BookID and perhaps CreditAs, so you can differentiate between Dr. Bob and Bob, PhD. (As well as pen names like Stephen King and Richard Bachman).

列将是 AuthorID、BookID 和 CreditAs，因此您可以区分 Dr. Bob 和 Bob, PhD。（以及像斯蒂芬金和理查德巴赫曼这样的笔名）。

And you can still uniquely identify the author.

而且您仍然可以唯一标识作者。

回答by Mitchel Sellers

It really looks like you are wanting to create a series of custom join tables, that are used to associate items from one entity to another.

看起来您确实想要创建一系列自定义连接表，用于将项目从一个实体关联到另一个实体。

I would start at the most granular level, person and say that ANY author must be a person. I would simplify that process.

我会从最细粒度的层面开始，说任何作者都必须是一个人。我会简化这个过程。

Create a person table with person information and a PersonId, put the information in there.

创建一个包含人员信息和 PersonId 的人员表，将信息放在那里。

THen create a BookAuthors table, with 3 columns BookId, PersonId, TitledName. THis way you can use a different name if needed, if not, you can use COALESE or something similar to get the default name if TitledName is null.

然后创建一个 BookAuthors 表，其中包含 3 列 BookId、PersonId、TitledName。通过这种方式，您可以在需要时使用不同的名称，如果不需要，如果 TitledName 为空，您可以使用 COALESE 或类似的东西来获取默认名称。

Just an idea..

只是一个想法..

回答by Cruachan

What you're asking really is not how you deal with 1..n relationships, but n..n relationships (as effectively on author and have many books, and one book can have many authors).

你真正要问的不是你如何处理 1..n 关系，而是 n..n 关系（对作者有效并且有很多书，一本书可以有很多作者）。

The classic way to handle this is via an intermediate table, so

处理这个问题的经典方法是通过一个中间表，所以

Author table (authorID, authorDetails) Book table (bookID, book details) AuthorBook table (authorID, bookID)

Author 表（authorID, authorDetails） Book 表（bookID，book details） AuthorBook 表（authorID，bookID）

If you're really bothered about changing author names then use a 1..n author details table, so add

如果您真的很担心更改作者姓名，请使用 1..n 作者详细信息表，因此添加

AuthorDetails (authorID, itemID, authorDetails)

作者详细信息（authorID、itemID、authorDetails）

and remove authorDetails from the author table

并从作者表中删除 authorDetails

回答by dakhota

a possible implementation in postgresql, just for the fun of it:

postgresql 中的一个可能实现，只是为了好玩：

create table books (
  book_id integer primary key,
  title varchar not null
);

create table aliases (
  alias_id integer primary key,
  alias varchar not null
);

create table books_aliases (
  book_id integer references books (book_id),
  alias_id integer references aliases (alias_id),
  primary key (book_id, alias_id)
);

create table authors (
  author_id integer primary key,
  author varchar not null,
  interesting boolean default false
);

create table aliases_authors (
  alias_id integer references aliases (alias_id),
  author_id integer references authors (author_id),
  primary key (alias_id, author_id)
);

create view books_aliases_authors as
  select * from books 
    natural join books_aliases
    natural join aliases
    natural join aliases_authors
    natural join authors;

one could use "using" instead of the natural join:

可以使用“使用”代替自然连接：

create view books_aliases_authors as
  select *
    from books 
    join books_aliases using (book_id) 
    join aliases using (alias_id) 
    join aliases_authors using (alias_id) 
    join authors using (author_id);

or do the complicated thing for mysql compatibility (note that mysql would also need an explicit maximal length for the varchars above):

或者为 mysql 兼容性做一些复杂的事情（请注意，mysql 还需要上述 varchars 的显式最大长度）：

create view books_aliases_authors as
  select b.book_id, title, l.alias_id, alias, t.author_id, author, interesting
    from books b
    join books_aliases bl on bl.book_id = b.book_id
    join aliases l on bl.alias_id = l.alias_id
    join aliases_authors lt on lt.alias_id = l.alias_id
    join authors t on t.author_id = lt.author_id;

this example doesn't use the "people" table, only an "interesting" flag to authors. please note that nothingchanges (structurally), if you rename "authors" to "people"

这个例子不使用“people”表，只使用“有趣”的标记给作者。请注意，如果您将“作者”重命名为“人员”，则没有任何变化（结构上）

回答by JeeBee

For 1..n relationship (author has many books, author has many aliases):

对于1..n关系（作者有很多书，作者有很多别名）：

1. Put a foreign key author_id in Books pointing at author.
2. Create a new table, author_aliases, to hold the aliases information.
3. Put a foreign key alias_id in Books pointing at alias (nullable if author details are defaul).
4. Put author_id foreign key in author_aliases.

1. 在 Books 中放置一个指向作者的外键 author_id。
2. 创建一个新表 author_aliases 来保存别名信息。
3. 在 Books 中放置一个外键 alias_id 指向别名（如果作者详细信息为默认值，则可为空）。
4. 将 author_id 外键放在 author_aliases 中。

If you wish, you can use intermediary tables to link authors and books, but with a 1..n mapping I don't think this is necessary.

如果你愿意，你可以使用中间表来链接作者和书籍，但是使用 1..n 映射我认为这没有必要。

For an n..m relationship (author has many books, book has many authors):

对于 n..m 关系（作者有很多书，书有很多作者）：

You would have to use an intermediary join table (author_id, alias_id, book_id) instead of foreign keys in the book table. You will want to keep the foreign key from aliases to author (for easy lookup of author aliases without having to go via all their books).

您将不得不使用中间连接表（author_id、alias_id、book_id）而不是 book 表中的外键。您将希望保留从别名到作者的外键（以便轻松查找作者别名，而无需查看他们的所有书籍）。

You can argue that in terms of scalability in the future this is a better way to start off as well, even if the initial specification says something is a 1..n relationship. You will find that specifications (or question) as given often are inadequate, so you will want to design in the general manner for when the specifications change or are clarified.

您可以争辩说，就未来的可扩展性而言，这也是一种更好的开始方式，即使最初的规范说明某些内容是 1..n 关系。您会发现给定的规格（或问题）通常是不充分的，因此您需要以通用方式进行设计，以应对规格更改或澄清时的情况。