Redunandant entries or not, in a normalized db

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
LDusan
Forum Commoner
Posts: 45
Joined: Sun Mar 08, 2009 5:03 am
Location: Belgrade, Serbia

Redunandant entries or not, in a normalized db

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Redunandant entries or not, in a normalized db

Post 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)
LDusan
Forum Commoner
Posts: 45
Joined: Sun Mar 08, 2009 5:03 am
Location: Belgrade, Serbia

Re: Redunandant entries or not, in a normalized db

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Redunandant entries or not, in a normalized db

Post 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.
LDusan
Forum Commoner
Posts: 45
Joined: Sun Mar 08, 2009 5:03 am
Location: Belgrade, Serbia

Re: Redunandant entries or not, in a normalized db

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Redunandant entries or not, in a normalized db

Post by Eran »

It can be done with one query, and there is no duplication of data.
LDusan
Forum Commoner
Posts: 45
Joined: Sun Mar 08, 2009 5:03 am
Location: Belgrade, Serbia

Re: Redunandant entries or not, in a normalized db

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Redunandant entries or not, in a normalized db

Post 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.
LDusan
Forum Commoner
Posts: 45
Joined: Sun Mar 08, 2009 5:03 am
Location: Belgrade, Serbia

Re: Redunandant entries or not, in a normalized db

Post by LDusan »

Thanks a lot for clarifying.
Post Reply