CODEWITHSUNDEEP
mysql
user1149867
user1149867

Reputation:

How should I structure the SQL database for a novel reading site?

I'm building a site where users will be able to read and save their progress for any book on the site (bookmark the chapter last read). What's a good way to structure the users table? Should I make a column for each book to store the current bookmark number or is there a much better way?

EDIT:

Thanks a lot for the example and rating suggestion hank! What do you think about this:

Users

Books

Book Rating

Book Genre

Book Progress and Rating

Is this method of storing individual chapters a good idea?

Upvotes: 1

Views: 477

Answers (1)

hank
hank

Reputation: 3768

For normalization, I would do something like this;

Users

  • ID
  • username
  • etc

Books

  • ID
  • title
  • etc

Genres

  • ID
  • Genre

Book Genre

  • Book
  • Genre

Book Progress

  • User
  • Book
  • Chapter/Page
  • Status (Wishlist, Not started yet, Reading, Finished)

This allows for multiple users tracking progress for the same book, which opens up for suggestions on what book a user should read next, based on what others have read.

Maybe you would want to allow the users to rate a book (or even a chapter, who know?!) Keep it normalized, that's the key!

https://en.wikipedia.org/wiki/Database_normalization

Upvotes: 1

Related Questions