Should stored financial transactions include some data redundancy?

Question

I am currently storing financial transactions in the following table (shortened for brevity):

id INT
start DATETIME
end DATETIME
rate INT
usage INT
usage_fee INT
amount INT
commission_pct INT
payout INT
currency VARCHAR(5)

The total amount a customer is charged is calculated as follows: amount = (end - start) * rate + usage * usage_fee

The platform takes a fee/commission of: commission = amount * (commission_pct / 100)

The payout the service provider receives is: payout = amount - commission

Now in the above table, storing payout is technically redundant, as it can be calculated as shown above. My question is, what the common way / convention is on storing these types of financial transactions regarding data redundancy?

For example, I am considering also storing the result of (end - start) * rate and usage * usage_fee separately in this table, in addition to their sum (amount).

The pros I see of doing this are:

• no (rounding) errors when sending the respective amounts to the "payment provider's API" i.e. whatever the user ends up paying is exactly the same as what is stored in the DB as opposed to sending computed values to said API
• easy querying and analyses

Are these pros valid or would you recommend me to completely normalize the table and compute the values whenever needed?

I know that normalizing the table is the correct answer according to "database design principles", but since I'm dealing with financial data, I'm not sure if I'm 100% comfortable using computed values.

---

Commission rate is not specific per transaction, but the commission_pct charged will most likely change in the future and thus it is stored along with each transaction. Of course it wouldn't be necessary to store the commission percentage if the actual "commission amount" is stored instead.

The commission_pct and rate used at that point in time are stored in the table, which is why changing these in the future for subsequent transactions is not a problem and existing transactions will never be edited. Furthermore, there is only one controlled application that has access to the database. If the formula changes in the future, not having the results computed and stored would be a problem. I'm leaning towards the application computing these values and storing both the "raw" and computed values in one fell swoop.

Answer 1

It's quite common to store the results of calculations like this so you record the fact of the financial transaction, not what you think should have happened. Also because storing the results allows you to perform adjustments and change the calculations over time.

Answer 2

One possible problem with storing the payout is where and how it is computed. Looks like it will be done in the application that inserts a transaction.

What prevents anyone with access to the table to insert a transaction without computing the payout? And what if an application updates a transaction, changing for example the rate or commission pct ? The stored payout would then be incorrect.

Some databases can prevent that using application-permissions, i.e. only applications in possession of the permission to update the table can do so.

There is the solution of not storing the computed payout but that has the issue that everyone needing the payout will have to compute it. Meaning that all must then use the same formula for that. And changing the formula means changing all those applications - if you know them all.

Either store the payout and compute it automatically via a trigger, or make it a VIRTUAL column that is automatically computed too (and requires no storage). – Albert Godfrind