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?
Redunandant entries or not, in a normalized db
Moderator: General Moderators
Re: Redunandant entries or not, in a normalized db
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)
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
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.
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
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
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
It can be done with one query, and there is no duplication of data.
Re: Redunandant entries or not, in a normalized db
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
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
Thanks a lot for clarifying.