Off-Platform Project: Designing a Database From Scratch - Personal Finance Tracker

Personal Finance Tracker: An Overview

The Personal Finance Tracker DB is designed to help users keep track of their accounts, deposits, withdrawals, and transfers with ease. This post will walk you through the database structure of the Personal Finance Tracker, explaining how it works and why certain features are beneficial.

Database Structure

The Personal Finance Tracker database consists of several tables, each serving a distinct purpose. Here’s a breakdown of the tables:

Users Table

This table holds information about the users of the system.

Column Type Constraints
id INTEGER PRIMARY KEY, GENERATED BY DEFAULT AS IDENTITY
username VARCHAR UNIQUE, NOT NULL
email VARCHAR UNIQUE, NOT NULL
created_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP)

Accounts Table

This table keeps track of the different accounts that users can create.

Column Type Constraints
id INTEGER PRIMARY KEY, GENERATED BY DEFAULT AS IDENTITY
name VARCHAR NOT NULL
description TEXT
created_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP)
budget FLOAT NOT NULL DEFAULT 0
user_id INTEGER FOREIGN KEY REFERENCES users(id)

Deposit/Withdraw Table

This table records all deposit and withdrawal transactions.

Column Type Constraints
id INTEGER PRIMARY KEY, GENERATED BY DEFAULT AS IDENTITY
value FLOAT NOT NULL DEFAULT 0
initialized_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP)
action_type CHAR(1)
category_id INTEGER FOREIGN KEY REFERENCES categories(id)
account_id INTEGER FOREIGN KEY REFERENCES accounts(id)

Categories Table

This table defines the different categories for transactions.

Column Type Constraints
id INTEGER PRIMARY KEY, GENERATED BY DEFAULT AS IDENTITY
title VARCHAR NOT NULL
description TEXT
created_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP)

Transfer Table

This table manages the transfer of funds between accounts.

Column Type Constraints
id INTEGER PRIMARY KEY, GENERATED BY DEFAULT AS IDENTITY
value FLOAT NOT NULL DEFAULT 0
sender_account INTEGER FOREIGN KEY REFERENCES accounts(id)
recipient_account INTEGER FOREIGN KEY REFERENCES accounts(id)

Table Relationships

The relationships between these tables ensure data integrity and efficient management:

  • Users to Accounts: One user can have multiple accounts (one-to-many).
  • Categories to Deposit/Withdraw: Transactions can belong only to one category (one-to-many).
  • Accounts to Deposit/Withdraw: Each account can have multiple transactions (one-to-many).
  • Accounts to Transfers: Accounts can send and receive transfers (one-to-many).

Triggers

Triggers are a powerful feature of SQL that allow automatic actions in response to certain events in the database. In the Personal Finance Tracker, triggers are used to ensure the accuracy of account balances during deposits, withdrawals, and transfers.

Deposit/Withdraw Trigger

This trigger updates the account balance whenever a deposit or withdrawal is made.

Function: handle_deposit_withdrawal

CREATE OR REPLACE FUNCTION handle_deposit_withdrawal()
RETURNS TRIGGER AS $
BEGIN
    IF (SELECT budget FROM accounts WHERE id = NEW.account_id) < NEW.value AND NEW.action_type = 'W' THEN
        RAISE EXCEPTION 'Insufficient funds in sender account.';
    ELSE
        IF NEW.action_type = 'W' THEN
            -- Handle withdrawal
            UPDATE accounts
            SET budget = budget - NEW.value
            WHERE id = NEW.account_id;
        ELSIF NEW.action_type = 'D' THEN
            -- Handle deposit
            UPDATE accounts
            SET budget = budget + NEW.value
            WHERE id = NEW.account_id;
        ELSE
            -- Prevent invalid action types
            RAISE EXCEPTION 'Invalid action_type: %. It must be either W (withdrawal) or D (deposit).', NEW.action_type;
        END IF;
    END IF;
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

Trigger: after_insert_deposit_withdrawal

CREATE TRIGGER after_insert_deposit_withdrawal
AFTER INSERT ON deposit_withdraw
FOR EACH ROW
EXECUTE FUNCTION handle_deposit_withdrawal();

Transfer Trigger

This trigger handles the transfer of funds between accounts, ensuring the sender has sufficient funds and the transfer is valid.

Function: handle_transfer

CREATE OR REPLACE FUNCTION handle_transfer()
RETURNS TRIGGER AS $
BEGIN
    IF NEW.sender_account = NEW.recipient_account THEN
        RAISE EXCEPTION 'Invalid transfer. Sender account cannot transfer to itself.';
    ELSE
        -- Ensure sender has sufficient budget
        IF (SELECT budget FROM accounts WHERE id = NEW.sender_account) < NEW.value THEN
            RAISE EXCEPTION 'Insufficient funds in sender account.';
        ELSE
            -- Subtract from sender's budget
            UPDATE accounts
            SET budget = budget - NEW.value
            WHERE id = NEW.sender_account;
            -- Add to recipient's budget
            UPDATE accounts
            SET budget = budget + NEW.value
            WHERE id = NEW.recipient_account;
        END IF;
    END IF;
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

Trigger: after_insert_transfer

CREATE TRIGGER after_insert_transfer
AFTER INSERT ON transfer
FOR EACH ROW
EXECUTE FUNCTION handle_transfer();

Why Triggers are Useful

Triggers are crucial in this use case because they automate the process of updating account balances, ensuring consistency and accuracy without requiring additional application logic. This means that every deposit, withdrawal, or transfer operation is automatically reflected in the account’s budget, preventing errors and maintaining data integrity.

Q&A

While doing this project I was speaking with my team leader about the structure and the functionalities of this DB and how did I use triggers to automate some tasks. He opened my eye on an issue similar systems like banks, ecommerce and collaborative applications may encounter, which is handling simultaneous operations. His question was how would your DB handle simultaneous withdrawals and transfers that may occur at the exact same time from multiple devices?
I have searched for ‘How to handle simultaneous DB operations in postgres?’ and the answer was with something called ‘Concurrency Control’. In short concurrency control is a technique that handles the simultaneous operations of multiple transactions. In PostgreSQL, this is primarily achieved through a mechanism called MVCC (Multi-Version Concurrency Control). When a user tries to withdraw money from their account using two devices simultaneously, we need to ensure that these operations are handled in a way that prevents race conditions and maintains the integrity of the account balance. Transactions with proper locking mechanisms can solve this issue.
In PostgreSQL, we can use SERIALIZABLE isolation level or explicit row-level locking with FOR UPDATE to handle such concurrency issues.
I have used the row-level locking with ‘FOR UPDATE’ which served my purpose. By using the FOR UPDATE clause in my SQL queries, I can lock the rows that are being read and modified during the transaction, ensuring that no other transactions can modify those rows until the lock is released.

Example Using Row-Level Locking:

BEGIN;

-- Lock the account row
SELECT budget
FROM accounts
WHERE id = 1
FOR UPDATE;

INSERT INTO transactions (account_id, amount, type, created_at)
VALUES (1, 100, 'withdrawal', current_timestamp);

COMMIT;
  1. A transaction is started with BEGIN.
  2. The SELECT ... FOR UPDATE statement locks the account row for the duration of the transaction.
  3. The transaction is committed with COMMIT and after the commit the locked row will be unlocked for the second transaction.

Conclusion

The Personal Finance Tracker is a powerful tool for managing personal finances, with a robust database structure and smart use of triggers to automate and ensure the accuracy of financial operations. If you’re interested in exploring the project further, feel free to check out the GitHub repository.

Thank you for reading, and happy budgeting!

1 Like