Database Schema - miketu926/GetStarted GitHub Wiki

users

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

• index on email, unique: true
• index on session_token, unique: true
• user_id has many projects
• user_id has many transactions
• user_id has many payment_infos

projects

column name	data type	details
id	integer	not null, primary key
project	string	not null, indexed, unique
description	string	not null
category	string	not null
location	string	not null
project_picture	string
goal_amt	integer	not null
funded_amt	integer	not null
duration_days	integer	not null
user_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on project, unique: true
• index on user_id
• user_id belongs to users
• project_id has many transactions
• project_id has many tiers

tiers

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

• index on project_id
• project_id belongs to projects

transactions

column name	data type	details
id	integer	not null, primary key
transaction_amt	integer	not null
user_id	integer	not null, indexed, foreign key
project_id	integer	not null, indexed, foreign key
payment_info_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on user_id
• index on project_id
• index on payment_info_id
• user_id belongs to users
• project_id belongs to projects
• payment_info_id belongs to payment_info

payment_info - BONUS

column name	data type	details
id	integer	not null, primary key
cc_num	integer	not null, indexed, unique
cardholder	string	not null
expiration	integer	not null
security_code	integer	not null
zip	integer	not null
user_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on cc_num, unique: true
• index on user_id
• user_id belongs to users
• payment_info_id has many transactions