Database design: Calculating the Account Balance

Question

How do I design the database to calculate the account balance?

1) Currently I calculate the account balance from the transaction table In my transaction table I have "description" and "amount" etc..

I would then add up all "amount" values and that would work out the user's account balance.

---

I showed this to my friend and he said that is not a good solution, when my database grows its going to slow down???? He said I should create separate table to store the calculated account balance. If did this, I will have to maintain two tables, and its risky, the account balance table could go out of sync.

Any suggestion?

EDIT: OPTION 2: should I add an extra column to my transaction tables "Balance". now I do not need to go through many rows of data to perform my calculation.

Example John buys $100 credit, he debt $60, he then adds $200 credit.

Amount $100, Balance $100.

Amount -$60, Balance $40.

Amount $200, Balance $240.

Solution 1

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.

Solution 2

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.

Solution 3

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.

Solution 4

Of course you need to store your current balance with each row, otherwise it is too slow. To simplify development, you can use constraints, so that you dont need triggers and periodic checks of data integrity. I described it here Denormalizing to enforce business rules: Running Totals

Solution 5

A common solution to this problem is to maintain a (say) monthly opening balance in a snapshot schema. Calculating the current balance can be done by adding transactional data for the month to the monthly opening balance. This approach is often taken in accounts packages, particularly where you might have currency conversion and revaluations.

If you have problems with data volume you can archive off the older balances.

Also, the balances can be useful for reporting if you don't have a dedicated external data warehouse or a management reporting facility on the system.

View more solutions

Comments

[TomTom]

Tell that the peopel coming to audit your bookkeeping. You will be surprised how long they laugh. Every transaction to an account musst be numbered (to maintain an order) and you can just put the new account balance right in there. No need for a second table actually. No performance penalty. Doubles, btw., how bookkeeping is done. Which this is all about.

[TomTom]

Ah... no, seriously. Accounting isa tricky busines to get right with possibly some hefty legal requirements. Ad hoc summations do not cut it.

[lanartri]

@TomTom: now I see your point. Your idea is good. Too bad it was not clearly said in your answer, and too bad your answers look so agresssive !

[Unreason]

@TomTom, Marcelo's wording is a bit ambiguous, but your comments on auditing are not helpful, nor right. Audit is about establishing correctness and have little to do with optimisation of speed. Marcelo is a bit imprecise because he talks about 'current balance', where in reality most financial systems will keep balances according to account and other analytical dimensions summed up by certain date granularity. Your idea of keeping a running balance on transaction level is useless for any report that would need to filter transactions on anything but attribute that conforms to order of booking.

[Unreason]

This give something extra to reconcile in an audit. Which helps audit the software (in terms of correct calculations and checking if transactions get deleted; and is not requirement=the only way to do it), which where I work is part of an IT audit; but this is not directly related to accounting audit (IT audit is just a part of financial audit).

[Dog Ears]

Correct, but it's not unlikely with large interoperateing system to encounter situations where certain process (an overnight batch for instance) creates anomalies; a bit of denormalisation, will help ensure that they can be tracked down.

[Unreason]

@Dog Ears, I see - so most of your entries come from data exchange/interoperation and then IT audit is much stronger component of financial audit. Interesting. Still I keep my position: this is an extra control value which would exist over all transactions. If I wanted to audit the data exchange process I would put controls that concentrate on it (using some sort of checksums of the data exchange: calculated by initiator and simply received, calculated on the received data in the received format - checks the integrity of transfer, calculated on the imported data - checks your import procedure)

[001]

"Personally, I prefer a credit field, a debit field and a signed amount, this makes reversals much easier to follow.", why not just have 1 field "Amount" with the sign?

[001]

"All the banking packages I've worked with store the balance with the account entity." how do they do that extactly? this would produce multiple account records as the balance are updated. Im looking at this diagram right now, databaseanswers.org/data_models/online_banking/index.htm It seems to be incomplete, but if this person was to store the balance information he would store it under the "Balance_history" table (to me this feels more like the right thing to do).

[001]

From his diagram, ... Transaction table stores the transaction information, including the "amount" field, and the balance history table, stores the updated balance. It feels somewhat right, however not feeling that its completely correct, ie How would I know which transaction was the transaction that affected the balance?

[smirkingman]

@001 "why not just have 1 field "Amount" with the sign?" I don't think you read my answer carefully. I said: "Personally, I prefer a credit field, a debit field and a signed amount"

[smirkingman]

@001 "databaseanswers.org/data_models/online_banking/index.htm". Don't take everything you find on the internet as gospel truth. That diagram, is, how should I put it, very naïve...

[smirkingman]

@dogs ears If you've never worked on core banking systems, you should refrain from giving (awful) advice

[Dog Ears]

@smirkingman for the benefit of the community what's awful about my advice, for that matter what makes it different from your advice? Seems that we basically suggested the same thing?

[001]

>>>"@001 "why not just have 1 field "Amount" with the sign?" I don't think you read my answer carefully. I said: "Personally, I prefer a credit field, a debit field and a signed amount"<<<---- Credit field, Debit field, and Amount field, this would mean you would have 3 fields, what exactly do you use the credit and debit field for? example.

[smirkingman]

@dogs ears the awfulness is storing the balance in every movement. More duplicated data and a nightmare to fix when something goes wrong.

[smirkingman]

@001 Each transaction contains 3 fields. 1/ The ID of the account being debited. 2/ The ID of the account being credited. 3/ The amount, positive=transfer from creditor to debitor, negative=transfer from debitor to creditor (usually a reversal). I suggest you read en.wikipedia.org/wiki/Double-entry_bookkeeping_system

[001]

If its done your way with credit and debit account id, do note that when money moves, sometimes there are more than two accounts involved. Example $5500 total from Account X, $5000 to Account Y, $500 to Account Z (TAX). What are you going to do in this case?

[smirkingman]

@001 That is 2 movements. 1/ X credits 5000 to Y. 2/ X pays 500 tax to Z. It must be done this way, as later, someone will ask 'show all the payments to the tax account Z' and you don't want the 5000 in that answer. I really suggest that you do a bit of background reading on book-keeping, you'll find it very helpful

[001]

In your solution... the amount is only negative for resverals everywhere else even in a payment or refund it's positive! Confirm if this is correct.

[001]

@smirkingman I like your solution better than mine actually, its more efficient than mine, just got to understand a little more about the amount sign.

[001]

@smirkingman, in your solution, amount sign could actually be all positive, even for transactions that are reserved, since you would have to add another entry anyways for reserval ie you will want to know how much and how often you reverse transactions. Is this correct? if not please state if the amount could be negative in any other entry besides reserved transactions and why exactly is it better? to have a negative sign.

[001]

I think the discussion is getting out of scope, hence I opened a new topic to address this question. stackoverflow.com/questions/4415022/…

[RayLuo]

@smirkingman and Mr. 001, thanks for this inspirational dialog. Just want to confirm, if I also need to record how many transactions ever happened for a specific account, would I have to create ONE EXTRA record for each transaction therefore double the transaction table size? For example, along with "$100 from account X (to account Y)", do I need "1 transaction happened for account X" too? Because I can not simply sum-up how many rows in transaction tables for that, when in future some of the old transactions would be moved out.

[smirkingman]

@iceberg If you need to know, forever, how many transactions were made, then you logically can never archive old ones: Suppose the client sees '560 transactions on this account'; it's then reasonable that he'll say 'show me them all'. If you must archive then keep the transaction count as a field in the account table (and be prepared for headaches a few years later ;-)

[RayLuo]

@smirkingman Sorry did not see your feedback in time. But your feedback doesn't comply with your own answer. Your answer suggests "an open balance on each account" and then "move old movements out of the active movement table to history table". In this case the client sees for example "560 dollars on my balance" but they won't get answer if they ask "show me how I end up this figure since I open this account 20 years ago", will they? And I am just following your idea. My question was specifically "do I need to store transaction counter as one more account". Can you share thoughts on this one?

[smirkingman]

@iceberg Your 'history' table is no different to your current transaction table. It's just in a different file, with a final balance record which allows you to 'pick up where you left off' without re-calculating the balance from those historical transactions. If you need a complete transaction history, you have to keep each transaction; the history serves to avoid re-reading them all on the occasional "what did I have 20 years ago?" question. The number of transactions is, in itself, of no interest; it's the balance that counts

[Axil]

hi, I am not clear with no.3. "opening balance" is in the same transaction table or a separate table ?

[PerformanceDBA]

@TomTom. Of course they do, if the Transactions are implemented according to Accounting Standards (yours aren't), in which case "ad hoc" is false. Besides, duplicating the CurrentBalance in every single row is plain stupid: when an adjustments has to be made, masses of rows have to be updated.

[PerformanceDBA]

This answer is incorrect, code-dependent, and cumbersome. For a method that (a) complies with audit requirements and legislature, (b) doesn't require triggers; offline safety nets; duplicate columns; duplicated data, and (c) performs well regardless of table population, look at this Answer.

[PerformanceDBA]

I see that you like your record filing systems with concrete poured into them.

[vwvolodya]

@smirkingman Can you please explain how the notion of "locking" money on a credit card can be added here? Often before money are taken as a payment they a locked first, which means they are still on your account but you cannot use them (as far as i know.)

[smirkingman]

@vwvolodya My account balance is $500 on the 1st of January. I reserve a room for the 10th, the Hilton locks $100 for my room, an anticipated debit. My available balance is $400, until either 1/ Hilton debits the $100 or 2/ the Hilton re-credits the blocked $100. On the 2nd of January I reserve a $100 room at the Ritz, my balance is $300, etc. On the 4th I cancel the room at the Hilton, they re-credit and my balance is $400. 'Locking' is a way of expressing "promise to pay at some future date"; promise=guarantee, so my account must be debited until the promise is either fulfilled or rescinded.

[vwvolodya]

@smirkingman yes, i understand how this concept works. I wanted to ask about applying this principle in the scope of this discussion and transaction table. Should locking be a different type of transaction? How would you implement locking in DB design? Thanks.

[smirkingman]

@vwvolodya I think that 'locking' is not the best word to describe money set aside for a future debit, as it suggests 'locking' at the database level. There is no 'lock' in a database sense, the fact that $100 have been debited ensures that the money is set aside. There is no difference between $100 debited "ahead of time" by the hotel and a straight $100 cash withdrawal, the money is debited up front. The only difference is when you cancel the booking and the hotel *re-credits* the 100$ later on.