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.