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.