How to Manage User Credit and Debit Transactions in MySQL

Learn how to handle credit and debit transactions in MySQL with real-world SQL examples. Start simple and scale to large apps with high-performance solutions.

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