8

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.

4
  • 2
    I'm not sure what flavour of SQL you are using, nor if this is strictly relevant but you should consider the type you use for storing and operating on financial data. INT is probably not the correct choice. Some SQL implementations provide a "money" type for final representation; which might be appropriate for you to use for storage. "Decimal" would likely be more appropriate for you to use for calculations. – JNB Jun 18 at 13:18
  • @JNB Thank you for your comment. I was actually once told to always use integers to represent financial data, as it prevents issues with having to round and store floats... I store all the financial data as cents and parse them to floats (/ 100) on the client-side – Marnix.hoh Jun 18 at 13:38
  • 2
    They validity of that does depend on the implementation and the use case, but you should be aware that different legal jurisdictions have different rules regarding the fractional rounding of financial calculations, if you can only represent money as integer cents, this may cause you some issues. From the calculations you provided above, look what happens if the commission percentage is 33 on a 10000 cent transaction. In the worst case, you could literally be breaking the law. – JNB Jun 18 at 13:57
  • @JNB Thank you for pointing that out. I will look into that! Not sure if this is relevant, but Stripe also uses cents in their API. So again I thought that using cents to store financial data is the general convention... – Marnix.hoh Jun 18 at 14:20
13

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.

1
  • 1
    Thank you so much for your answer! You perfectly put in words, what I was trying to say when I said that "I am not 100% comfortable using computed values": it is better to record the fact instead of what I think should have happened. – Marnix.hoh Jun 17 at 21:24
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.