Page 1 of 1

Redunandant entries or not, in a normalized db

Posted: Mon Feb 08, 2010 8:31 pm
by LDusan
I have normalized bookstore db with authors and books in separate tables and one join table with authors and books IDs. However there is one problem, some books have authors listed as Authorname1, Authorname2 and coauthors. These coauthors are problem, how should I treat them? As one author? For example:

Book1 - Peter Peterson

Book2 - Peter Peterson, John Johnson & coauthors

Book3 - Mark Markson & coauthors

Obviously coauthors from Book1 and Book2 are not the same. How many rows in authors table would this be?

The way I see it I can either treat all coauthors as one author with one ID, or give each coauthors its own ID. Any ideas?

Re: Redunandant entries or not, in a normalized db

Posted: Mon Feb 08, 2010 8:51 pm
by Eran
a many-to-many table between authors and books.

authors_to_books
- book_id
- author_id

You can also add a status column if you want to indicate different kinds of authors on the same book (such as primary author, co-author, etc)

Re: Redunandant entries or not, in a normalized db

Posted: Tue Feb 09, 2010 3:06 am
by LDusan
I already made database that is exactly like that.

The thing is I don't know the name of the coauthors, it's like when the book has 10 authors, the label says Author1, Author2 and coauthors. Author1, Author2 - ok, two entries in the authors table (if they don't already exist) and two entries in join table, but how do I treat these other authors credited as "coauthors"? I have no other info about them.

Re: Redunandant entries or not, in a normalized db

Posted: Tue Feb 09, 2010 3:22 am
by Eran
If you don't know their identity, you can't treat them beyond the book row. Add a boolean column to the book table indicating whether a book has additional co-authors with unknown identity.

Re: Redunandant entries or not, in a normalized db

Posted: Tue Feb 09, 2010 5:24 am
by LDusan
That seems like the most logical solution. But it will most likely cost another query and theoretically compromise data structure. Thanks a lot.

Re: Redunandant entries or not, in a normalized db

Posted: Tue Feb 09, 2010 7:08 am
by Eran
It can be done with one query, and there is no duplication of data.

Re: Redunandant entries or not, in a normalized db

Posted: Tue Feb 09, 2010 7:17 am
by LDusan
Yes but I thought that theoretically all author related data should be in authors table. Maybe this situation is not considered as author related.

Re: Redunandant entries or not, in a normalized db

Posted: Tue Feb 09, 2010 7:25 am
by Eran
Specific authors reside in the author table. The extra indication whether a book has additional co-authors without reference to specific authors, is a book property.

Re: Redunandant entries or not, in a normalized db

Posted: Tue Feb 09, 2010 12:07 pm
by LDusan
Thanks a lot for clarifying.