For those involved in the financial sector, data storage

Question

I am creating an application that stores financial information of users in a sqlite database. I want him to store all the information, such as account number, bank name, interest rates, etc.

I wanted to ask how the following is done in real financial software. As an example, when someone requests data from a database through software, the software simply goes and retrieves the data, or the software goes and gets the basic information, and then calculates the data in place.

If we want to see the payment amount, and we know that this is a certain percentage, we store the payment amount in a table or simply calculate it on the spot.

If I would like to query the database for the total accrued interest, I store this data in a table or compute it in place.

I just have problems understanding that it’s better to keep the database table simple and do most of the calculations in place, or to store more data in the table, and the software fills it in the background.

Answer 1

As a rule, in all industries we do not store data that can be calculated, because otherwise it is a violation of the normal forms of the database. If one piece of data is updated, it should all be updated.

Be careful as data may be calculated initially, but may not change with other data.

Like the current interest rate, but when awarding interest payments you may want to keep the interest rate they earned because the interest rate they earned is constant, but the current interest rate is not.

Answer 2

" Invariable invoices " will to some extent talk about it. While some cannot store additional data, some records are probably best exported to some kind of archive in case of things like price changes that should not retroactively change invoices.

Answer 3

I write financial software for banks.

I can’t say what is the correct answer, but this is what I saw and what works.

Depending on the complexity of the calculation (.5 s or 2 hours / write), the code can either calculate the result "on the fly" when querying from the database, or get the already calculated result from another table. It is very useful to store all the variables that go into the calculation separately, if necessary, return the result.

In many cases, calculations include real-time bets and financial information, and there is no time (when the calculation is difficult) to update all records with correctly calculated results. This is usually done in night parties, but in real time there is no update.

Answer 4

Well, keep in mind that you will need to record a lot of history related to rate changes and associate your financial records with potentially many different rates. We retain the ability to generate all calculations from scratch if necessary (and regularly coordinate), but usually we have historical aggregate positions that are updated periodically (new entry for each week or month), and the calculations are based on changes that have occurred after them. It is not always convenient to create everything from scratch in an instant.

Answer 5

Usually (not always, obviously), when working with financial data, you will find some pre-calculated data in almost all ERPs. When you need to move millions of transactions, and you need to make a lot of calculated columns and aggregates, shopkeepers and the data obtained by this data will be useful. You can find this data as additional columns in the tables where the source data is located, or in separate tables that store aggregates or intermediate operations.

Answer 6

In a financial application, data fields are usually packaged in some standard formats. These data fields contain user authentication data. The packed data is sent to the receiving server, and a response code is generated in response. The response data is packaged in a standard format and sent to the client side, the code defined on the client side must correspond to the response code for a successful transaction. Thus, work continues in financial programs.