* Dan Jones
[...]
> This leads me to another question. What's the standard way of handling
> something like the author of a book when you have a collaboration or an
> anthology with multiple authors?
Normalizing.
> The only way I can think of to handle it is to have a sentinel value
> that indicates multiple authors, then have another table which lists the
> primary key of the books table and associates it with multiple authors.
Right. :)
> For example:
>
> BookID AuthorID
> 15 22
> 15 39
> 15 43
> 27 03
> 27 94
I would add a counter, to separate between 'primary' and 'additional'
authors:
15 22 1
15 39 2
15 43 3
27 03 1
27 94 2
> This would show that book 15 had three authors - 22, 39 and 43 while
> book 27 had two authors - 03, 94. Of course, this would vastly
> complicate displaying or printing the database.
A little, but it's worth it.
> It would also make it
> difficult for searches for a particular author to find books with
> multiple authors, etc. Is there an easier way to design the database
> which deals with these issues?
Using an extra LEFT JOIN you can check if there are any Authors with a
Counter=2:
SELECT Authors.*, Books.*,
IF(more_authors.AuthorID,'yes','no') AS more_authors
FROM Authors,Author_Books,Books
LEFT JOIN Author_Books AS more_authors ON
more_authors.AuthorID = Authors.AuthorID AND
more_authors.BookID = Book.BookID AND
more_authors.Counter = 2
WHERE
Authors.AuthorID = Author_Books.AuthorID AND
Books.BookID = Author_Books.BookID AND
Author_Books.Counter = 1
Book.Title like "%summer%";
This would list all books matching "%summer%", including the name of the
'primary author' and a yes/no indication if there are more authors for this
book.
When listing books for a specific author, you distinguish between 'primary
author' and 'co-author' by checking if Counter=1:
SELECT Author.*,Book.*,
IF(Counter=1,'author','co-author') AS Role
FROM Authors,Author_Books,Books
WHERE ...
HTH,
--
Roger
