Database Schema

Database Schema

Users

column name	data type	details
id	integer	not null, primary key
name	string	string(100), not null
email	string	string(50), not null, unique
password	string	string(50), not null
created_at	datetime	not null
updated_at	datetime	not null

Friends

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

• many-to-many join table between Users

Friends Expenses

column name	data type	details
id	integer	not null, primary key
payer_id	integer	not null, foreign key
receiver_id	integer	not null, foreign key
description	string	string(1000), not null
amount	integer	not null
expense_date	datetime	not null
settled	boolean	not null, default = False
notes	string	string(200)
created_at	datetime	not null
updated_at	datetime	not null

• payer_id owes the money

Expense Payments

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

Comments

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, foreign key
expense_id	integer	not null, foreign key
comment	string	string(200)
created_at	datetime	not null
updated_at	datetime	not null

BONUS

Groups

column name	data type	details
id	integer	not null, primary key
creator_id	integer	not null, foreign key
name	string	string(100), not null
created_at	datetime	not null
updated_at	datetime	not null

Group Memberships

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

Group Expenses

column name	data type	details
id	integer	not null, primary key
group_id	integer	not null, foreign key
description	string	not null
amount	integer	not null
settled	boolean	not null, default = False
expense_date	datetime	not null
notes	string	string(200)
created_at	datetime	not null
updated_at	datetime	not null

Group Expense Memberships

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, foreign key
expense_id	integer	not null, foreign key
is_receiving	boolean	not null, default = True
amount	integer	not null
created_at	datetime	not null
updated_at	datetime	not null

Group Expense Payments

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

• separate from Expense Payments due to potentially conflicting expense_ids