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 :(

Biological

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.

Sammons

Answer 2

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

Killigrew

Answer 3

You have two real possibilities:

• Many authors per book, but each author can have at most one book (highly unlikely) - this way, you'll put a foreign key in the Author table, linking that author to the book. You could have two such authors with the same book ID, but it means you don't have to duplicate the book details or, actually, anything more than the book's ID
• More likely: Many authors per book, and many books per author. This is called a "many to many relationship", and requires that you add another table.

So, let's think about that for a second: a book has all its details, and an ID. Each author has many details, and an ID. If you want to associate an author to a book, you add a row to another table, let's say called "Credits".

• Each author can have many credits, but each credit is for a single author.
• Similarly, each book can have many credits on the front cover, but each credit is only on that book.

That way you have two one-to-many relationships (from author to credits, and from book to credits), and can represent what you need without any awkward multi-values.

Kreitman

Answer 4

You need another table called BookAuthor, you don't need the Authors column in Books

BookAuthor
------------
BookID
AuthorID

Where the BookID references the PK (BookID) on Books and AuthorID references PK (AuthorID) on Authors

Select b.Title, CONCAT(a.[First Name], a.[Last Name]) As AuthorName
From Books b
JOIN BookAuthor ba ON b.ID = ba.BookID
JOIN Authors a ON ba.AuthorID = a.ID

Orion