Database Schema

Database Schema

User

ID PK int username string email string hashPassword string

Book

ID PK int Cover title author published avg_rating statusId FK >- readStatus.ID

readStatus

ID PK int status string

tag

ID PK int category

books-to-tags

bookId FK >- Book.ID tagId FK >- tag.ID

Bookshelf

ID PK int name userId FK >- User.ID

Review

ID PK int author raiting created_at userId FK >- User.ID bookId FK >- Book.ID

bookshelf-to-books

bookshelfId FK >- Bookshelf.ID bookId FK >- Book.ID

---

user

column name	data type	details
id	integer	not null, primary key
username	string	not null,
email	string	not null, indexed, unique
created_at	datetime	not null
updated-at	datetime	not null

• index on email, unique: true
• userId references users table

book

column name	data type	details
id	integer	not null, primary key
cover	string	not null,
title	string	not null, unique
author	string	not null
avg rating	integer	not null
rating	integer	not null
shelves	string	not null
review	text	not null

• index on email, unique: true
• userId references users table