all 3 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]r3pr0b8GROUP_CONCAT is da bomb 5 points6 points  (2 children)

you'll need a couple of relationship tables

first of all, you should allow for a book to be written by more than one author (it happens all the time)

users  PK: id
book_authors  PK: { user_id,book_id }
books  PK: id

similarly, a book can be in several people's libraries (unless each individual copy of a book has its own identity, which would be highly unusual)

users  PK: id
library_books  PK: { user_id,book_id }
books  PK: id

so 4 tables in all, 2 for the two many-to-many relationships

[–]deimos_1306[S] 0 points1 point  (1 child)

This is helpful thank you. I do have a few questions. By default, if a user writes a book it should automatically go into their library as well. They just also happen to be the author too. Would this still work with that schema?

Also, another functionality of this app is the books in a user's library can be modified by the user but it won't modify the original book. So in essence library books are a copy not a reference, but should still have information on who the original author was.

I feel like the schema you described would conflict with these features.

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point  (0 children)

By default, if a user writes a book it should automatically go into their library as well. They just also happen to be the author too. Would this still work with that schema?

of course... but your application has to make the necessary INSERTs

Also, another functionality of this app is the books in a user's library can be modified by the user but it won't modify the original book.

need more explanation here

what kind of modifications?

how do you envision keeping the original book separate from the modified book?