Database Schema

Database Schema

Users

column name	data type	details
id	integer	not null, primary key
name	string	not null
email	string	not null, unique
password	string	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	not null
amount	integer	not null
expense_date	datetime	not null
settled	boolean	not null, default = False
notes	string
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
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	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
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