Database design: Calculating the Account Balance

An age-old problem that has never been elegantly resolved.

All the banking packages I've worked with store the balance with the account entity. Calculating it on the fly from movement history is unthinkable.

The right way is:

• The movement table has an 'opening balance' transaction for each and every account. You'll need this in a few year's time when you need to move old movements out of the active movement table to a history table.
• The account entity has a balance field
• There is a trigger on the movement table which updates the account balances for the credited and debited accounts. Obviously, it has commitment control. If you can't have a trigger, then there needs to be a unique module which writes movements under commitment control
• You have a 'safety net' program you can run offline, which re-calculates all the balances and displays (and optionally corrects) erroneous balances. This is very useful for testing.

Some systems store all movements as positive numbers, and express the credit/debit by inverting the from/to fields or with a flag. Personally, I prefer a credit field, a debit field and a signed amount, this makes reversals much easier to follow.

Notice that these methods applies both to cash and securities.

Securities transactions can be much trickier, especially for corporate actions, you will need to accommodate a single transaction that updates one or more buyer and seller cash balances, their security position balances and possibly the broker/depository.

You should store the current account balance and keep it up to date at all times. The transaction table is just a record of what has happened in the past and shouldn't be used at a high frequency just to fetch the current balance. Consider that many queries don't just want balances, they want to filter, sort and group by them, etc. The performance penalty of summing every transaction you've ever created in the middle of complex queries would cripple even a database of modest size.

All updates to this pair of tables should be in a transaction and should ensure that either everything remains in sync (and the account never overdraws past its limit) or the transaction rolls back. As an extra measure, you could run audit queries that check this periodically.

This is a database design I got with only one table for just storing a history of operations/transactions. Currently working as charm on many small projects.

This doesn't replace a specific design. This is a generic solution that could fit most of the apps.

id:int standard row id

operation_type:int operation type. pay, collect, interest, etc

source_type:int from where the operation proceeds. target table or category: user, bank, provider, etc

source_id:int id of the source in the database

target_type:int to what the operation is applied. target table or category: user, bank, provider, etc

target_id:int id of the target in the database

amount:decimal(19,2 signed) price value positive or negative to by summed

account_balance:decimal(19,2 signed) resulting balance

extra_value_a:decimal(19,2 signed) [this was the most versatile option without using string storage] you can store an additional number: interest percentage, a discount, a reduction, etc.

created_at:timestamp

For the source_type and target_type it would be better to use an enum or tables appart.

If you want a particular balance you can just query the last operation sorted by created_at descending limit to 1. You can query by source, target, operation_type, etc.

For better performance it's recommended to store the current balance in the required target object.