To those in the Financial Sector, storage of data question

Question

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.

Answer 1

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.

Answer 2

"Immutable Invoices" would be a tale of caution to some extent on this subject. While one may not want to store extra data, some records are probably best exported into some archive in case of things like price changes that shouldn't retroactively change invoices.

Answer 3

I write financial software for banks.

I can not say what is the completely right answer, but this is what I have seen and what works.

Depending on the complexity of the calculation (.5s or 2 hours / record) the code can either calculate result on the fly when queried from the database or fetch an already calculated result from another table. It is very useful to store all of the variables that go into calculation separately, in case there is a need to backtrace the result.

In many cases the calculations include real-time rates and financial information and there is not time (when calculation is complex) to update all of the records with the properly calculated results. Those are usually done in overnight batches, but lack the real-time update.