DB Schema

Database Schema

Users 👤

column name	data type	details	key
id	integer	NOT NULL	PK
username	string	NOT NULL
email	string	NOT NULL, UNIQUE
hashed_password	string	NOT NULL
created_at	timestamp	NOT NULL
updated_at	timestamp	NOT NULL

SQLAlchemy Associations

• a User has many Cities
• a User has many Insights
• a User has many Helpfuls

Cities

column name	data type	details	key
id	integer	NOT NULL	PK
name	string	NULL
thumbnail_img	string	NULL
user_id	integer	NOT NULL	FK
created_at	timestamp	NOT NULL
updated_at	timestamp	NOT NULL

SQLAlchemy Associations

• Recipes belongs to Users
• Recipes has many Comments

Insights

column name	data type	details	key
id	integer	NOT NULL	PK
insight	string	NOT NULL
user_id	integer	NOT NULL	FK
city_id	integer	NOT NULL	FK
created_at	timestamp	NOT NULL
updated_at	timestamp	NOT NULL

SQLAlchemy Associations

• Insights belongs to Cities
• Insights belongs to Users

Questions

column name	data type	details	key
id	integer	NOT NULL	PK
question	string	NOT NULL
answer	boolean	NOT NULL
scale	integer	NULL
user_id	integer	NOT NULL	FK
category_id	integer	NOT NULL	FK
created_at	timestamp	NOT NULL
updated_at	timestamp	NOT NULL

SQLAlchemy Associations

• Questions belongs to Users

Matches ✅

column name	data type	details	key
id	integer	NOT NULL	PK
city_id	integer	NOT NULL	FK
user_id	integer	NOT NULL	FK
updated_at	timestamp	NOT NULL

SQLAlchemy Associations

• Matches hasMany Users
• Matches hasMany Cities

Helpfuls 👍

column name	data type	details	key
id	integer	NOT NULL	PK
user_id	integer	NOT NULL	FK
recipe_id	integer	NOT NULL	FK
created_at	timestamp	NOT NULL
updated_at	timestamp	NOT NULL

SQLAlchemy Associations

• Helpfuls belongs to Users
• Helpfuls belongs to Cities

BONUS

CityPhotos 📷

column name	data type	details	key
id	integer	NOT NULL	PK
video_url	string	NULL
img_url	string	NULL
city_id	integer	NULL	FK
created_at	timestamp	NOT NULL
updated_at	timestamp	NOT NULL

Profile

column name	data type	details	key
id	integer	NOT NULL	PK
status	string	NULL
bio	string	NULL
profile_img	string	NULL
location	string	NULL
created_at	timestamp	NOT NULL
updated_at	timestamp	NOT NULL

SQLAlchemy Associations

• CityPhotos belongs to Cities