Schema

Postgres Database Schema

users

column name	data type	details
id	bigint	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
• has_many games_in_cart through cart_items
• has_many :games
• has_many :reviews

games

column name	data type	details
id	bigint	not null, primary key
title	string	not null, indexed, unique
price	float	not null
genre	string	not null
details	text	not null
created_at	datetime	not null
updated_at	datetime	not null

• index on title, unique: true
• has_many :reviews

carts

column name	data type	details
id	bigint	not null, primary key
user_id	string	not null, foreign key, indexed
game_id	float	not null, foreign key, indexed
created_at	datetime	not null
updated_at	datetime	not null

• user_id reference to user
• game_id reference to game
• index on [:game_id, :user_id], unique: true
• has_many :games
• belongs_to :user

reviews

column name	data type	details
id	bigint	not null, primary key
user_id	string	not null, foreign key, indexed
game_id	float	not null, foreign key, indexed
recommended	boolean	not null
comment	text	not null
created_at	datetime	not null
updated_at	datetime	not null

• user_id reference to user
• game_id reference to game
• index on [:game_id, :user_id], unique: true
• belongs_to :game
• belongs_to :user

games_library

column name	data type	details
id	bigint	not null, primary key
user_id	string	not null, foreign key, indexed
game_id	float	not null, foreign key, indexed
created_at	datetime	not null
updated_at	datetime	not null

• user_id reference to user
• game_id reference to game
• index on [:game_id, :user_id], unique: true