I have been struggling trying to find a way to properly and reliably maintain a balance for accounts in an order/payment system.
Currently, the process consists of three tables. Below example is very simplified but gets the point across.
Table one contains the account ID, name, total paid, total orders, balance etc.
Table two contains payments, date, the account ID (FK) and amount.
Table three contains orders, date, the account ID (FK) and amount.
Calculating the balance is of course as easy as
SELECT SUM(payments.AMOUNT) FROM payments WHERE ID = [account]
SELECT SUM(orders.AMOUNT) FROM orders WHERE ID = [account]
Followed by some simple addition/subtraction math:
UPDATE accounts SET BALANCE = [balance] WHERE ID = [account]
All good - the problem is - how do I maintain this over let's assume years of activity and many, many transactions?
Currently the accounts table is updated every single time a transaction is made, meaning the above three statements run every time. There is of course an index on the ID column in all three tables, and everything is fast and neat for now, but I work with less than 100,000 transactions, meaning everything fits into the RAM and basically all I throw at the database gets executed immediately. Over time this will change, of course - so how do I handle this?
Is it better to mark the accounts as "dirty" and process balancing in a batch job later, or can I keep updating the balance real-time even when I reach the point where the orders and payments tables will not fit into the RAM?
Do I need to segment the orders and payments and "lock" everything before a given time, such as before last calculation? (I would rather not). I ask because then I could do a SUM WHERE DATE > [last lock timestamp] and update with UPDATE BALANCE = [balance] + [value for last lock] and prevent the problem from outgrowing resources.
I don't need a running balance for each transaction. That's all I can find results for when I try to research this problem. I just care about the total amount paid, the total amount ordered and the balance of accounts.
Why don't I calculate on the fly when I need the balance, you ask? Because one must be able to query the system and ask "Which of my (let's say) 50.000 accounts have a balance below 0?" - something we cannot do easily if we do not store the balance with every account.