如何在简单的书表中写下数据库多个作者？ [英] How write down to database multiple authors in simple books table?
问题描述
我想知道如何在简单数据库中保存作者（如果有多个作者）。
I am wondering how should I save authors (in case where there are more than one author) in simple database.
如果一本书有一个作者，一切都很容易。问题是当我想要表图书，并且希望可以通过表作者进行简单选择和联接：
In case one one book has one authors, everything is easy. The problem is when I want to have table Books and want to have possibility of making simple selects and joins with table Authors :
图书
| id | Title | ISBN | Authors? |
---------------------------------
| | | | |
| | | | |
作者
| id | Firs Name | Last Name | Short Name | Pseudonym |
--------------------------------------------------------
| | | | | |
| | | | | |
我知道我可以使用相同的ISBN和TITLE分开的行，
I know that I can use separate rows with this same ISBN and TITLE, however I wondering is it good idea in case when title can be sometime very large (255-length UTF string/varchar/char).
我知道这可能是一个非常基本的问题，所以很抱歉，如果标题可能有时很大（255长度的UTF字符串/ varchar / char）我必须问一下：（
I know that probably this is very basic problem, so sorry that I have to ask about that :(
推荐答案
由于一本书可以有多个作者，而且作者可以写多本书，我建议在图书和作者表格之间有多对多的关系。
Since a book can have multiple authors, and an author can write more than one book, I'd suggest a many-to many relationship between the Books and Authors tables.
添加另一个链接两个表的表，如下：
Add another table which links the two, like so:
BookAuthors
| BookID | AuthorID |
| BookID | AuthorID |
BookID是Books.id的外键，AuthorID是Authors.id的外键。
BookID is a foreign key to Books.id and AuthorID is a foreign key to Authors.id.
如果要返回给定图书的所有作者，您可以按照以下行程执行：
If you want to return all of the authors for a given book, you could do something along the lines of:
SELECT Authors.id, Authors.FirstName, Authors.LastName
FROM Authors INNER JOIN BookAuthors ON Authors.id = BookAuthors.AuthorID
WHERE BookAuthors.BookID = '123'
这篇关于如何在简单的书表中写下数据库多个作者？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
