CODEWITHSUNDEEP
sqlpostgresqlselect
musztardus
musztardus

Reputation: 47

Many foreign keys in one column

I have a books database in Postgres, and I want to add an authorId column, but I don't know how to make it work when a book has many authors.

Author table

authorId    firstName    lastName
1           Terry        Pratchett
2           Neil         Gaiman

Book table

bookId    name             authorId
1         Good Omens       1, 2

How do I make it work? My first idea was storing authorId as an array.

My desired query would look something like this

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

and it would return this

firstName    lastName
Terry        Pratchett
Neil         Gaiman

But I have a feeling that it won't work, and there is a better way to do it.

Upvotes: 2

Views: 69

Answers (3)

pedram bashiri
pedram bashiri

Reputation: 1376

Yes, the fact that you're using postgres gives you the great option of storing authorIds as an array. You can define your table like:

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

And your select query should be

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

Upvotes: 0

Mureinik
Mureinik

Reputation: 311188

The textbook solution would be to have an additional "mapping" table to map books and authors. This way an author may author several books, and a book may have several authors:

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)
)

Then you can use a join to query the authors of a book:

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

Or if you don't have the book's id, another join with books will allow you to query its details:

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'

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

m:n relations (one author can write many books + one book can be written by many authors) are best modelled with a bridge table:

  • author (author_id, first_name, last_name)
  • book (book_id, title)
  • book_author (book_id, author_id)

The query to get the authors of book #1:

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

Upvotes: 0

Related Questions