Database schema design of Splitwise application

Splitwise gained vast popularity among travellers and friend groups. I built quicksplit, which works like splitwise and I want to share the schema design for a splitwise-like expense sharing and expense management application.

The application allows users to share expenses in groups and track how much they owe to each other. It enables users to create groups, manage expenses, track debts, and settle payments with ease. Let us have a look on how we can design the database for this.

Project features

• User Authentication: Secure registration, login, and logout.
• Group Management: Create and manage groups with customisable members.
• Expense Management: Add, categorise, and split expenses among group members.
• Debt Tracking: Automatic calculation and tracking of balances between users.
• Settlement and Payment Tracking: Manual recording of settlements and payments.
• Notifications: Email notifications for group activities like expense creation and for payment reminders.
• Reporting: Generate and download payments report.

Database design

Let’s dive into the schema of this application. Here is the image for your reference:

Users table:

This contains users information- you can also choose phone number instead of email or even both.

Groups and Members:

The groups table contains the groups and group details. Similarly, the members table contains the members of each group. The created_by in groups references id from users table. In members table, users_id references the id from users table and group_id references id from groups table.

Expenses:

This table tracks the expenses made in each group. It stores expense description, amount and the creator of the expense.

Balances:

This table stores the updated balances among the users. It keeps track of who owes how much to whom. One can find out the amount to be paid between two users in a single query through this table.

Payments:

To record the settlements, we need this table. It stores the payment between two users. It also has a unique payment_id column so that users can cross check in case of discrepancy.

Reminders(optional):

This records the payment reminders or notifications sent to the users. This supports the integration of sending payment reminders functionality. Admins can track how many email/mobile notifications were sent successfully and what were it’s results.

Conclusion

I hope you enjoyed the article, any suggestions/improvements are welcome.

Reach out to me at [e-mail] for any questions, feedback, or collaboration opportunities.