How are account balances persisted

Question

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.

Answer 1

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.

Answer 2

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.)

Answer 3

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

Answer 4

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.