sql server - 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.

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