I am creating application which stores the users financial information in a sqlite database. I want it to store all sorts of info like account number, bank name, interest rates, etc.
I wanted to ask how the following is done in real financial software. As an example when somebody requests data from the database via the software, does the software just go and retrieve the data or does the software go and get the fundamental information and then calculate on the spot the needed data.
If we wanted to see a payment amount and we know such is a certain percent, do we store the payment amount in the table or do we just calculate it on the spot.
If I wanted to query the database for total accrued interest, do I store this data in the table or do I calculate it on the spot.
Im just have trouble understanding if its better to keep the database table simple and do most calculations on the spot, or to keep more data on the table and have the software populate it in the background.
Generally, in all industries, we don't store data that can be calculated, because otherwise, it's a violation of database normal forms. If one piece of data gets updated, they all have to get updated.
Be careful though, because data might be initially calculated, but might not change with other data.
Like there might be a current interest rate, but when awarding interest payments, you might want to store the interest rate that they earned, because the interest rate they earned is constant, but the current interest rate isn't.