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

Well, bear in mind you'll need to record lots of history relating to changes in rates and link your individual financial entries to potentially many different rates. We maintain the ability to generate all calculations from scratch if needed (and reconcile regularly), but generally we have historic aggregate positions which are updated periodically (a new entry for each week or month) and calculations are based on the changes since them. It isn't always practical to generate everything from scratch in an instant.

Answer 3

Usualy (not always, obviously), when working with finantial data you will find some kind of pre-catched calculated data in almost all ERPs. When you have to move millions of transaction and you have to make lots of calculated columns and aggregates, the beneficts of store and mantain this data calculated will be beneficial. You may find this data as aditional columns in the tables where the original data is, or in separate tables storing aggregates or intermediate operations.

Answer 4

"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.