一列中有许多外键

Question

我在Postgres中有一个书籍数据库，我想添加一个authorId列，但是当一本书有很多作者时，我不知道如何使其工作。

作者表

authorId    firstName    lastName
1           Terry        Pratchett
2           Neil         Gaiman

书桌

bookId    name             authorId
1         Good Omens       1, 2

我如何让它工作？我的第一个想法是将authorId存储为数组。

我想要的查询看起来像这样

SELECT firstName, lastName 
FROM author 
WHERE authorId IN 
(SELECT authorId 
FROM book 
WHERE bookId=1);

它会返回这个

firstName    lastName
Terry        Pratchett
Neil         Gaiman

但我有一种感觉，它行不通，有更好的方法可以做到这一点。

Answer 1

教科书的解决方案是有一个额外的"映射"表来映射书籍和作者。这样，作者可以创作几本书，一本书可以有多个作者：

CREATE TABLE book_authors (
author_id INT NOT NULL,
book_id INT NOT NULL,
PRIMARY KEY book_authors_pk (author_id, book_id),
FOREIGN KEY author_fk (author_id) REFERENCES authors (author_id),
FOREIGN KEY book_fk (book_id) REFERENCES books (book_id)
)

然后，您可以使用join查询书籍的作者：

SELECT first_name, last_name
FROM   authors a
JOIN   book_authors ba on a.author_id = ba.author_id
WHERE  book_id = 1

或者，如果您没有图书的 ID，另一个包含books的联接将允许您查询其详细信息：

SELECT first_name, last_name
FROM   authors a
JOIN   book_authors ba ON a.author_id = ba.author_id
JOIN   books b ON ba.book_id = b.book_id
WHERE  b.name = 'Good Omens'

Answer 2

m：n 关系(一个作者可以写很多本书 + 一本书可以由多个作者写(最好用桥表建模：

• 作者(author_id、first_name、last_name(
• 书籍(book_id，书名(
• book_author(book_id、author_id(

获取书籍 #1 作者的查询：

select first_name, last_name
from author
where author_id in (select author_id from book_author where book_id = 1);

Answer 3

是的，你使用 postgres 的事实为你提供了将 authorId 存储为数组的绝佳选择。您可以像这样定义表：

CREATE TABLE Book (
bookId serial not null,
name varchar,
authorIds int[]
);

您的选择查询应该是

SELECT firstName, lastName 
FROM author 
WHERE authorId IN 
(SELECT unnest(authorIds) 
FROM Book 
WHERE bookId = 1);