Postgres Database Schema

USERS

column name	data type	details
id	integer	not null, unique
username	string	not null, indexed
email	string	not null, indexed, unique
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, null: false
• index on name
• index on session_token, unique: true

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

• index on user_id, null: false
• index on friend_id, null: false

EXPENSES

column name	data type	details
id	integer	not null, primary key
belong_to_group_id	integer	not null, foreign key
expense	float	not null
description	string	not null
category	string	not null
note	string	not null
author_id	integer	not null, foreign key
expense_date	date	not null
expense_split_percentage	float	not null
paid	boolean	default: false
created_at	dateTime	not null
updated_at	dateTime	not null

*index on author_id, null: false *expense_split_percentage could vary choose by user.

GROUP EXPENSES TOTAL AND SPLIT

column name	data type	details
id	integer	not null, primary key
split_amount	float	not null
receiver_id	integer	not null, foreign key
receiver_paid	boolean	default: false
created_at	dateTime	not null
updated_at	dateTime	not null

• index on id(expense id), null: false
• index on receiver_id , null: false

GROUP TABLE

column name	data type	details
id	integer	not null, primary key
group_name	string	not null
group_type	string	not null
created_at	dateTime	not null
updated_at	dateTime	not null

• group_member associate to many current users. ( has_many association)
• index on group_name, null: false

GROUP MEMBERS TABLE

column name	data type	details
id	integer	not null, primary key
group_id	integer	not null, foreign_key
group_member_id	integer	not null, foreign_key
created_at	dateTime	not null
updated_at	dateTime	not null

• users can have many groups and group can have many users.

COMMENT

column name	data type	details
id	integer	not null, primary key
comment	string	not null
author_id	integer	not null, foreign_key
expense_id	integer	not null, foreign_key
created_at	dateTime	not null
updated_at	dateTime	not null