A system (in Financial Services) that I have worked on had an overnight batch job that would calculate all balances at that time based on the sum of transactions since the last time the process was run (usually daily but not always) and the balance at that time - the "opening balance". Updates to the system were turned off whilst these calculations were on-going.
The point was to find a balance (if you will excuse the pun) between the overhead of doing calculations from day 0 against the storage of data and updating it.
The key is that you must have some means of determining the "last time the balance was updated" - yes you could have a separate table with it's own ID but personally I would just store the "balance at date" and "last balance date" on the accounts table (in our system each customer could have many accounts).
I don't agree with
Quote:
There are two tables, Withdrawal and Deposit. Each store customerID, amount and date.
To me it makes more sense to have a "Transaction" table with CustomerID, Amount (which can be negative for Withdrawals) and Transaction Date. Transactions in the banking arena would also have a reference field (free format text) and a unique identifier.
Submit an article or tip