Schema - SouVangLee/FishNGames GitHub Wiki

users

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

• index on email, unique: true
• index on session_token, unique: true
• A user has many reviews
• A user has many cartItems

products

column name	data type	details
id	integer	not null, primary key
name	string	not null, indexed
price	integer	not null
quantity	integer	not null
description	text	not null
category_id	integer	not null, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• A product belongs to a category
• A product has many reviews

reviews

column name	data type	details
id	integer	not null, primary key
review	text	not null
product_id	integer	not null, foreign key
rating	integer	not null, indexed
reviewer_id	integer	not null, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on rating
• index on [product_id, reviewer_id] unique: true
• A review belongs to a user
• A review belongs to a product

categories

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

• A category has many products

cartItems

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

• index on [user_id, product_id], unique: true
• A cartItem belongs to a user

orders

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

• user_id references users
• cart_id references cartItems

favorites

column name	data type	details
id	integer	not null, primary key
favorite_user_id	integer	not null, foreign key
favorite_product_id	integer	not null, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• favorite_user_id references users
• favorite_product_id references products
• index on [favorite_user_id, favorite_product_id], unique: true