Database Schema

Users

column name	data type	details
id	integer	not null, primary key
email	string(50)	not null, unique
fullName	string(100)	not null
hashedPassword	string	not null
createdAt	datetime	not null
updatedAt	datetime	not null

• index on email, unique: true

GameGuides

column name	data type	details
id	integer	not null, primary key
title	string(255)	not null
author	string(100)
releaseDate	dateonly
publisher	string(255)
summary	text
coverImg	string
createdAt	datetime	not null
updatedAt	datetime	not null

Reviews

column name	data type	details
id	integer	not null, primary key
rating	integer	not null
reviewText	text	not null
userId	integer	not null, foreign key
gameGuideId	integer	not null, foreign key
createdAt	datetime	not null
updatedAt	datetime	not null

• userId references Users table
• gameGuideId references GameGuides table

Statuses

column name	data type	details
id	integer	not null, primary key
name	string(50)	not null, unique
createdAt	datetime	not null
updatedAt	datetime	not null

• index on email, unique: true

StatusShelves

column name	data type	details
id	integer	not null, primary key
statusId	integer	not null, foreign key
userId	integer	not null, foreign key
gameGuideId	integer	not null, foreign key
createdAt	datetime	not null
updatedAt	datetime	not null

• statusId references Statuses table
• userId references Users table
• gameGuideId references GameGuides table

CustomShelves

column name	data type	details
id	integer	not null, primary key
name	string(50)	not null
userId	integer	not null, foreign key
gameGuideId	integer	foreign key
createdAt	datetime	not null
updatedAt	datetime	not null

• userId references Users table
• gameGuideId references GameGuides table