Database Schema

[djsiddz]

We need to store the users and their books.

For users, we need some basic information like:

• ID (auto-generated)
• Name
• Email
• Password (encrypted)
• Profile Picture
• Active

For books, we need the following information:

• User ID foreign key
• ID (auto-generated)
• Title
• Subtitle
• Cover Image
• Series Name (optional)
• Series Number (optional)
• Author
• Format (Physical, PDF/e-book, Kindle)
• Category (Fiction, Art, Mathematics, Music, Biographies, Autobiographies, etc) See List Here
• Publication
• ISBN
• Acquisition Type (Purchased, Gifted)
• Acquisition Date
• Acquisition Store
• Acquisition Price
• User Summary
• User Rating
• Reading Status

We can normalize the books schema so that this data can be shared amongst users, and we reduce data duplication.

We can have a relation table booksOwned, where we can move the below details, as these are specific to the unique book ownership and not the book itself.

For booksOwned we can have the following information:

• User ID (foreign key)
• Book ID (foreign key)
• Format (Physical, PDF/e-book, Kindle)
• Acquisition Type (Purchased, Gifted)
• Acquisition Date
• Acquisition Store
• Acquisition Price
• User Summary
• User Rating
• Reading Status

That leaves the following information in the books schema:

• ID (auto-generated)
• Title
• Subtitle
• Cover Image
• Series Name (optional)
• Series Number (optional)
• Author
• Category (Fiction, Art, Mathematics, Music, Biographies, Autobiographies, etc) See List Here
• Publication

---

Next Steps

• [x] Add users schema to Supabase Database
• [x] Add books schema to Supabase Database
• [x] Add booksOwned schema to Supabase Database

[djsiddz]

If in a future scope, we want to show users their reading progress historically and kind of like a streak on a calendar, how many days they've read, then we will need some additional structure to enable it.

[djsiddz]

Keeping isbn and format fields with books schema instead of booksOwned schema.

[djsiddz]

Added profiles in place of users to avoid confusion with auth.users table. Keeping only first_name, last_name and screen_name fields in the profiles table.

[djsiddz]

Added booksOwned table. Will record final schema created here for future reference.

[djsiddz]

Reference here: https://supabase.com/docs/guides/getting-started/quickstarts/nextjs

[djsiddz]

Rethinking the schema as I have learnt some better things. I am missing a lot of book information that I get from Open Library / Google Books API which I am not capturing. Also missing - book covers, and authors.