This is perhaps the most obvious approach and the implementation is fairly simple. After every transaction is executed the appropriate accounts should be debited/credited as part of that unit of work.

The benefit of doing it this way is that when we want to get the latest balance for an account we can easily do it without the need for any calculations. The balance stored in the database is always accurate.

The problems begin when the number of transactions being executed starts to increase. The likelihood is that most transactions will involve one of the bank’s house accounts which means that you very quickly end up with severe database locking issues.

These can theoretically be solved by using row level locking but it’s only a matter of time before the performance of the system is completely non existent.

The alternative is to not store the balance in real time and instead have a batch job running each night (for example) that updates it until the end of that day.

This approach was first shown to me by a colleague who also pointed out this article by Pat Helland on the benefits of bringing the ideas of accountancy into software design.

The trade off that we incur as a result of taking this approach is that we now need to create a batch job to calculate how the balances have changed each day and update these in the database.

Finding the current balance for an account also becomes more difficult as we now need to take the previous day’s balance and apply the impact of today’s transactions to come up with a real time balance.

The problems with database locking are removed, however, and this approach scales much more easily.

There are other interesting challenges around handling data like this in financial systems but balances strike me as particularly interesting since the most obvious solution is not necessarily the best one.