I have a dilemma I am not sure how to handle. I have a web based client tracking system for social charities. one of the functions in the package is a loan tracking module. These people are not banks, these are small loans, and most of the time it's interest free.
The dilemma is how to handle the balance. (the old aggregate problem). If I want to provide the ability to fix a prior payment, it seems like storing a balance anywhere is going to be problematic when changing entries that are not the last one in the list.
My theoretical studies of Databasing says you don't store that which you can compute. I am about to the point of not having a current balance anywhere, just compute the balance by taking the loan amount and deducting the principle payments for each payment. This would give me the flexibility to change payment 3 of 8 perhaps, and be able to compute the current balance to any given payment occurrence by running the loan payment rows. I doubt there would ever be more than 10 payments for a particular loan and the loan_id in the payment segment is indexed, meaning the SQL call would be relatively efficient,
I have been messing with data bases for about 30 years (IMS-DL/1 - Focus, SQL Access/DB even dBase). How to handle aggregates has always been a stichy wicket and I have seen it handled different ways. I am looking for some fresh ideas to an age old problem.
Theoretically, no, you should not store any value you can compute.
But "In Theory, Theory and Practice are the same, but in Practice, Theory and Practice are different."
If your database becomes very large, then recalculating the same value over and over again is an obvious waste of CPU.
Plus, from an accounting standpoint, you should never modify old payment entries anyway. Every change should be done as a new adjustment record. There may even be legal requirements prohibiting you from deleting or modifying financial records.
So, store the balance as a snapshot of the account after every transaction, and set up adjustment records for handling corrections. You could also write a stored procedure that would quickly recalculate all the point-in-time balances for any single account, should that become necessary.
If it's not practically useful, then it's practically useless.