DEV Community

Saksham Negi
Saksham Negi

Posted on

1

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:

DB schema diagram

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 sakshamnegi.dev@gmail.com for any questions, feedback, or collaboration opportunities.

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay