Schema

Project Schema

Users Table

Column Name	Data Type	Details
id	integer	not null, primary key
username	string	not null, indexed, unique
email	string	not null, indexed, unique
password_digest	string	not null
session_token	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

• index on username, unique: true
• index on email, unique: true
• index on session_token, unique: true

Games Table

Column Name	Data Type	Details
id	integer	not null
title	string	not null, indexed
description	text
developer	string	not null
publisher	string	not null
price	integer	not null
release_date	date	not null
featured	boolean	not null, indexed, default false
created_at	datetime	not null
updated_at	datetime	not null

• index on title
• index on featured, default false
• price in cents

Game-Images Table

Column Name	Data Type	Details
id	integer	not null
game_id	integer	not null, indexed, foreign key
img_url	string	not null
img_type	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• index on game_id, foreign key (connects to games table)
• img_type can be either "title-card" or "screenshot"

Purchases Table

Column Name	Data Type	Details
buyer_id	integer	not null, indexed, foreign key
game_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• joins table between users and games
• indexes on buyer_id and game_id, presence: true
• index on [buyer_id, game_id], unique: true
• buyer_id is a foreign key, connects to the users table
• game_id is a foreign key, connects to the games table

Reviews Table

Column Name	Data Type	Details
id	integer	not null
author_id	integer	not null, indexed, foreign key
game_id	integer	not null, indexed, foreign key
recommended	boolean	not null
body	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• indexes on author_id and game_id, presence: true
• index on [author_id, game_id], unique: true
• author_id is a foreign key, connects to the users table
• game_id is a foreign key, connects to the games table
• recommended defaults to false

Bonus Schema

Reactions Table

Column Name	Data Type	Details
id	integer	not null
reacter_id	integer	not null, indexed, foreign key
review_id	integer	not null, indexed, foreign key
reaction_type	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• indexes on reacter_id and review_id, presence: true
• index on [reacter_id, review_id, reaction_type] unique: true
• reacter_id is a foreign key, connects to the users table
• review_id is a foreign key, connects to the reviews table