How write down to database multiple authors in simple books table?

Question

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 :

Books

| id  | Title | ISBN | Authors? | 
---------------------------------
|     |       |      |          |
|     |       |      |          |

Authors

| id  | Firs Name | Last Name | Short Name | Pseudonym | 
--------------------------------------------------------
|     |           |           |            |           |
|     |           |           |            |           |

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

I know that probably this is very basic problem, so sorry that I have to ask about that :(

Answer 1

Get rid of the authors column in books.

You have a many to many relationship between books and authors: some books have multiple authors, and some authors wrote more than one book. And, books have first, second, and third authors, and so forth. You don't get to render the author names for a book in some unpredictable order. The authors and the publisher decide the author order, not the dbms programmer.

So, you need a books_authors table with the following columns

  book_id
  author_id
  author_ordinal   (a number like 1,2,3 to denote the order of authors)

You can get a list of authors for a particular book with this query:

 SELECT isbn, title, author_ordinal, first, last
   FROM books b
   LEFT JOIN books_authors ba ON (b.id = ba.book_id)
   LEFT JOIN authors a ON (ba.author_id = a.id)
  WHERE isbn = '978whatever'
  ORDER BY author_ordinal

You'd also be wise to put a text field called role in your books_authors table if you want to make your software bibliographically complete. People have various roles in the creation of books like 'author,' 'illustrator,' 'editor,' 'series editor,' 'contributor,' 'preface writer,' and so on. The role column will let you capture that information.

By the way, most dbms reverse engineering tools will be MUCH happier if you name your id columns consistently throughout. So you should use books.book_id and authors.author_id instead of just books.id and authors.id.