Database Schema

users

column name	data type	details
id	integer	not null, primary key
username	string	not null, indexed, unique
email	string	not null, indexed, unique
profile_image_url	string
password_digest	string	not null
session_token	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

books

column name	data type	details
id	integer	not null, primary key
title	string	not null, indexed
author	string	not null, indexed, foreign key
page_count	integer
published_date	date
cover_img_url	string
description	text
created_at	datetime	not null
updated_at	datetime	not null

reviews

column name	data type	details
id	integer	not null, primary key
book_id	integer	not null, indexed, foreign key
reviewer_id	integer	not null, indexed, foreign key
rating	integer	not null unless review is not null
title	string
body	text	not null unless rating is not null
created_at	datetime	not null
updated_at	datetime	not null

• book_id is a foreign key for the books table.
• reviewer_id is a foreign key for the users table.

bookshelves

column name	data type	details
id	integer	not null, primary key
name	string	not null
user_id	integer	not null, indexed, foreign key
book_ids	array	foreign key
created_at	datetime	not null
updated_at	datetime	not null

• user_id is a foreign key for the users table.
• book_ids is an array of foreign keys for the books table.