List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:August 9 2003 9:35am
Subject:Re: Updating table based upon matching field in second table
View as plain text  
* 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

Thread
Updating table based upon matching field in second tableDan Jones9 Aug
  • Re: Updating table based upon matching field in second tableMatthew McNicol9 Aug
    • Re: Updating table based upon matching field in second tableDan Jones9 Aug
      • Re: Updating table based upon matching field in second tableRoger Baklund9 Aug
  • Filtering special-charsSteffen Gebert9 Aug
    • Re: Filtering special-charsMatthew McNicol9 Aug