Database Schema

Database Schema

users

column name	data type	details
id	integer	not null, primary key
username	varchar	not null, unique
email	varchar	not null, unique
hashed_password	varchar	not null
created_at	datetime	not null
updated_at	datetime	not null

genres

column name	data type	details
id	integer	not null, primary key
type	varchar	not null
created_at	datetime	not null
updated_at	datetime	not null

pros

column name	data type	details
id	integer	not null, primary key
content	varchar	not null
created_at	datetime	not null
updated_at	datetime	not null

cons

column name	data type	details
id	integer	not null, primary key
content	varchar	not null
created_at	datetime	not null
updated_at	datetime	not null

games

column name	data type	details
id	integer	not null, primary key
name	varchar	not null
description	varchar	not null
year_released	integer	not null
developer	varchar	not null
platforms	varchar	not null
image_url	varchar	not null
genre_id	integer	not null, foreign key
created_at	timestamp	not null
updated_at	timestamp	not null

• genre_id references genres table

game_shelves

column name	data type	details
id	integer	not null, primary key
title	varchar	not null
user_id	integer	not null, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• user_id references users table

shelves

column name	data type	details
id	integer	not null, primary key
game_id	integer	not null
game_shelf_id	integer	not null
created_at	timestamp	not null
updated_at	timestamp	not null

• game_id references games table
• game_shelf_id references game_shelves table

reviews

column name	data type	details
id	integer	not null, primary key
title	varchar	not null
score	integer	not null
description	varchar	not null
user_id	integer	not null, foreign key
game_id	integer	not null, foreign key
pro_id	integer	not null, foreign key
con_id	integer	not null, foreign key
created_at	timestamp	not null
updated_at	timestamp	not null

• user_id references users table
• game_id references games table
• pro_id references pros table
• con_id references cons table