I am currently building a finance application that contains "accounts"

Each account has a balance that is dynamically generated based on platform charges vs payments. e.g simplified example.

$account->balance = $account->transactions->sum('amount') + ($account->charges->sum('amount')) 

It's obviously very simple however as the data is not persisted it is impossible for to run queries, sort by balance on.

Is it best practice to store a static balance for accounts ? I can obviously update the balance using events whenever a new charge or transaction is reconciled against the account. I would be interested to know solutions to this problem.

The application is Laravel but I don't believe that is important in this scenario.

  • Account balances are "persisted" the same way that any other value is persisted. – Robert Harvey Oct 11 at 17:06
  • as the data is not persisted it is impossible for to run queries -- Not entirely impossible. You can do it in SQL using GROUP BY, SUM and subqueries. Performance may be less than stellar, though. – Robert Harvey Oct 11 at 18:05
up vote 4 down vote accepted

Most commercial accounting software store the account balance, sometimes with the account, but most often in periodic tables (per year or per month).

The technical point of view

There are several reasons that justify this decision:

  1. Performance: One of my former customers had several million transactions posted every month. Imagine the database load if every balance would be recalculated at every query.
  2. High volumes: Many big companies address performance issues through the archival of older transactions on less expensive storage and reload them only when needed (i.e. tax controls). In this case, the totals of the account balance cannot be recalculated solely with data remaining in the main system.
  3. Legal obligations: accounting is strongly regulated and most countries around the world require a minimum retention period for transactional data. But beyond that time frame, companies use to discard unneeded transactions in order to avoid unnecessary archival costs. More and more, data protection requirements come on top of that as a new legal argument towards discarding data that is no longer legally required. So old transactions are discared, but balances shall not change due to that reason.
  4. Different access needs on different time horizons: The interest in transactional data tends to decreasing quickly over time once the transaction finished. It is rare that an accountant needs to access detailed transactions of 2 or 3 years ago. But interest in monthly and yearly balances remains high over a longer period for the sake of financial management. By the way, this explains why the periodic tables are the most common way to store the balances: once the carry forward into the next month done, no change anymore on previous balances.

The business point of view

There are also business reasons that justify to handle an account balance as a real value in the domain model.

The account domain is interested mainly in 3 category of informations

  • the financial situation at a point in time (i.e. the last day of the fiscal year, fiscal quarter, reporting month) documented by the account balances at that date.
  • the evolution of the financial situation over a period ( i.e. fiscal year, quarter, month). Usually, this is documented by comparing balances between the end and the start date of the reference period.
  • the transaction of a periods that justify the balance.

When an auditor audits the account statements, he/she will not take the 150 years of historical transactions since the foundation of the company to recalculate the value of the balance sheet. The auditor will take the previously audited balance sheet, add the total of the transactions in the audited period, and check if it matches the new balances.

Otherwise stated, for accountants and auditors, a balance, once audited or published, has a value for its own, independently of the transactions that explain/justify it.

Conclusion

Any real-life accounting system stores balances in some ways. Storing balances is not a denormalization when considered in the reality of the accounting model, but a feature required by accounting theory and practice. It acknowledges the view, in which balances are considered as an independent value (the financial situation), and transactions are only a documentation of how this value changes over time. Accounting systems that do not store balances are in general either study projects, or targeted at small companies.

  • 1
    Nice answer, and yes, I would agree with your summary "balance, once audited or published, has a value of its own.."; however, i believe the OP's problem, from comments he stated in response elsewhere, is that there are accounts that are not up-to-date, meaning they are missing transactions. – Chan-Ho Suh Oct 13 at 15:53
  • Thanks so much for the answer :) it makes a lot of sense ! I will be utilising this method. – Daniel Benzie Oct 14 at 0:09

If by "persisted" you mean "stored into a (not necessarily relational) database", then in general you shouldn't store derived values along with the primary values. This is called denormalization and is usually a bad trade-off; the derived values would have to be updated whenever primary data change, which multiplies the effort for writing and doesn't save all that much time for reading the balance; adding and subtracting a few things is very, very cheap comparing to accessing persistent storage, so reading the primary values and computing a bit is likely to take a similar time as reading a balance value.

(Denormalization is usually used when you do often want to read aggregated values and rarely or never write new primary data, e.g. in data warehouses.)

  • 3
    ... or for performance reasons. It can be rather computationally intensive to compute a balance using an account's entire historical record of transactions. – Robert Harvey Oct 11 at 17:03
  • 1
    Hi thanks for the response ! My DB is relational and I do need to be able to filter by the account balances. Reporting will be required on accounts that are not up to date for example. I could generate these reports by looping through each model and calculating the balance and returning a subset but this will be expensive. Currently I am generating the values as the model is accessed but I have just been asked for this level of reporting and am trying to think of ways to produce it. I thought storing the value would be bad and you have confirmed it. do you know of any solutions – Daniel Benzie Oct 12 at 10:23
  • In the real accounting life, account balances are not just the sum of the transactions. In the accounting domain, balances can be considered as an independent value, and transactions are only a documentation of how this value changed over time. – Christophe Oct 13 at 9:57

If you check you bank statements you'll see the first line is usually 'balance carried over from previous month'

I suggest you implement a similar 'snapshot' system, where you roll up previous transactions in an accounting period and 'close' that peroid so it won't change if out of order transactions come in at a later date.

You can then use the carried forward balance plus the transactions from the current accounting period to show the current balance.

When reporting you can use the balances as of the end of the accounting period. Which will be fixed and not require any summation of transactions.

Reporting of current values will be inaccurate in any case as presumably you are using thr sum of transactions method due to uncertainies in the timing of transactions

The way that I went about doing this is splitting Transaction from Account. Then implemented a service that was responsible for calculating the account balance. It looks something like this (sorry for C# syntax):

public class Account
{
    public Account(AccountID id, string name)
    {
    }

    public AddSubAccount(Account subAccount)
    {
    }

    public RemoveSubAccount(Account subAccount)
    {
    }
}

public class Transaction
{
    public Transaction(AccountID creditAccountID, AccountID debitAccountID, DateTime date, decimal amount)
    {
    }

    public DateTime Date;
    public decimal Balance;
    // other methods and properties
}

// domain service
public AccountBalanceCalculator
{
    public AccountBalanceCalculator(IAccountRepository accountRepo, ITransactionRepository transRepo)
    {
    }

    public decimal Balance(AccountID accountID)
    {
        // get account from Repo
        // get transactions for account
        // get all subaccounts for account
        // get all transactions for each subaccount
        // return sum
    }
}

This is a quick and dirty way. You could add a Sum() method on the ITransactionRepository that returns the sum of all transactions with a specific AccountID so you do not need to load all the transactions into memory.

Then when a Transaction is modified (e.g. the amount), you could fire off an event from the Transaction.ChangeAmount(decimal newAmount) method. This could be used to let the UI know that a transaction has changed.

If you want go even further, you could use CQRS. Then when the Write model (i.e. the domain model) changes, like Transaction, you could fire off an even that then modifies the ReadModel.

  • Hey thanks for your answer :) No worries on the C# syntax. Easily readable! My modelling is like below: An account can have many transactions and many charges. I have a service which collects these values and returns the balance for any given account which is great and works well. However the PM has just informed us that we need to be able to report on accounts that have (for example) a negative balance. Doing so at the moment would be difficult to achieve because the balance is not "stored" making querying by it inaccessible. What I am wondering is how/where to store the balances. – Daniel Benzie Oct 12 at 10:29

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.