Database Schema

users

column_name	data type	details
id	integer	not null, primary key
fname	string	not null
lname	string	not null
email	string	not null, indexed, unique
address	string	indexed
phone_number	string	not null, indexed, unique
password_digest	string	not null
session_token	string	not null, indexed, unique
current_order	integer
provider	string
uid	string
name	string
image	text
created_at	datetime	not null
updated_at	datetime	not null

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

stores

column_name	data type	details
id	integer	not null, primary key
name	string	not null, indexed, unique
address	text	not null
hours	text[]	not null
description	text
status	ENUM	not null
coordinate	point	not null, indexed
rating	decimal	not null
description	integer	not null
price_rating	text
created_at	datetime	not null
updated_at	datetime	not null

• index on :name
• :status could be created, active, inactive

carts

column_name	data type	details
id	integer	not null, primary key
contents	json	not null
status	ENUM	not null
customer_id	integer	not null, indexed, foreign key
store_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• :customer_id references users
• :store_id references restaurants
• index on :store_id
• index on :customer_id
• :status could be pending, completed, or cancelled

menus

column_name	data type	details
id	integer	not null, primary key
title	string	not null
rank	integer	not null
store_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on [:store_id, :rank]
• :store_id references stores
• index on :store_id

menu_items

column_name	data type	details
id	integer	not null, primary key
menu_id	integer	not null, foreign key
item_id	integer	not null, foreign key
rank	integer	not null
store_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on [:menu_id, :item_id] unique: true
• index on [:menu_id, :rank] unique: true

items

column_name	data type	details
id	integer	not null, primary key
name	string	not null, indexed
description	text
price	float	not null
store_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on [:store_id, :name] unique: true

categories

column_name	data type	details
id	integer	not null, primary key
category_title	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

• index on :category_title unique: true

store_categories

column_name	data type	details
id	integer	not null, primary key
store_id	string	not null, indexed, unique
category_id	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

• index on [:store_id, :category_id], unique: true
• index on [:category_id, :store_id]

reviews

column_name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign key
store_id	integer	not null, indexed, foreign key
rating	integer	not null, indexed
content	text
created_at	datetime	not null, indexed
updated_at	datetime	not null

• index on [:user_id, :store_id]
• index on :user_id
• index on :store_id
• index on :rating
• index on :created_at