Managing User Credit and Debit Transactions in MySQL

How to handle financial logic without rounding errors. Proper schema design for balances, transaction history, and ensuring data integrity.

Introduction

Managing user balances through credit and debit transactions is a common feature in many apps — whether you're building a wallet system, points tracker, or order system.

In this guide, you'll learn:

• How to structure credit and debit transactions in MySQL
• How to calculate user balance
• How to set up your database for large-scale usage

🔧 Step-by-Step Guide (Beginner Setup)

1. Create the Transactions Table

CREATE TABLE transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type ENUM('credit', 'debit') NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2. Insert Some Dummy Data

-- Credits
INSERT INTO transactions (user_id, type, amount) VALUES (101, 'credit', 200);
INSERT INTO transactions (user_id, type, amount) VALUES (101, 'credit', 150);

-- Debits
INSERT INTO transactions (user_id, type, amount) VALUES (101, 'debit', 50);
INSERT INTO transactions (user_id, type, amount) VALUES (101, 'debit', 70);

3. Calculate the Balance

SELECT 
  user_id,
  SUM(CASE WHEN type = 'credit' THEN amount ELSE 0 END) AS total_credit,
  SUM(CASE WHEN type = 'debit' THEN amount ELSE 0 END) AS total_debit,
  (SUM(CASE WHEN type = 'credit' THEN amount ELSE 0 END) -
   SUM(CASE WHEN type = 'debit' THEN amount ELSE 0 END)) AS balance
FROM transactions
WHERE user_id = 101
GROUP BY user_id;

Limitations of This Setup

The above method is simple and works well for testing or small apps. But it becomes slow if:

• You have millions of transactions.
• Thousands of users check their balances at the same time.
• You need real-time performance.

Scalable Solution for Large Applications

1. Create a user_balances Table

CREATE TABLE user_balances (
  user_id INT PRIMARY KEY,
  balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total_credit DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total_debit DECIMAL(10,2) NOT NULL DEFAULT 0.00
);

2. Update Balances Automatically

✅ Option A: Application Logic
Every time a transaction is added, update the balance table in your backend code. Recommended for better control and rollback support.

✅ Option B: MySQL Trigger(Auto-update)

DELIMITER //
CREATE TRIGGER after_transaction_insert
AFTER INSERT ON transactions
FOR EACH ROW
BEGIN
  IF NEW.type = 'credit' THEN
    INSERT INTO user_balances (user_id, balance, total_credit)
    VALUES (NEW.user_id, NEW.amount, NEW.amount)
    ON DUPLICATE KEY UPDATE 
      balance = balance + NEW.amount,
      total_credit = total_credit + NEW.amount;
  ELSE
    INSERT INTO user_balances (user_id, balance, total_debit)
    VALUES (NEW.user_id, -NEW.amount, NEW.amount)
    ON DUPLICATE KEY UPDATE 
      balance = balance - NEW.amount,
      total_debit = total_debit + NEW.amount;
  END IF;
END;
//
DELIMITER ;

3. Optimize for High Volume

• ✅ Index transactions.user_id, type, created_at
• ✅ Use BIGINT for storing amount in cents (₹10.00 = 1000)
• ✅ Archive old transactions or partition by month/year
• ✅ Optionally use message queues (RabbitMQ, Kafka) and workers

4. Reporting / Analytics

SELECT COUNT(*) FROM transactions WHERE user_id = 101 AND type = 'debit';

But for fast dashboards, use the precomputed data in the user_balances table.

Final Thoughts

This scalable strategy ensures:

• Fast dashboard loading
• Zero delay in balance calculation
• Smooth experience for users