DEV Community

Sjors van Dongen
Sjors van Dongen

Posted on

4 Errors I Made When Building A Financial Application

1. Not knowing which datatype to use in MySQL

I once heard it’s better to use integers when handling financial data. You convert a price like €10 to its lowest unit (cents in this case). This way you end up using 1000 as the amount to work with. This way you avoid the floating-point problem. The floating-point problem is best shown by typing the following in your Google Chrome console:

0.1 + 0.2 > 0.30000000000000004

If you want to learn more about this problem visit this website. Working with integers is dramatic for readability (how much is 13310 in euros?). The disadvantage of working with integers is also that it has a limit of 2147483647 which is roughly € 21,474,836.47. Although with the euro you probably wouldn’t run into this issue quickly but with the Vietnamese Dong, this wouldn’t work. Learnings: use decimals (not floats!) in MySQL to store monetary values. Depending on how many decimals you need decimal(15,2) oftentimes is enough.

2. Not having something to fact-check the numbers

Imagine we have a shopping cart where there’s 1 product for € 100, the VAT of € 21 and a total of € 131. The first time you’re sharp and you immediately see your mistake. After the 100th time, you start to be blind to those mistakes.

That’s why you need something to fact-check the numbers if they’re correct. I’ve created a Google Sheet for me and my team where we can all fact-check this. Especially if you work with people who test your product but don’t have access to the code this is crucial. How should they know if the price displayed is the correct one?

3. Not splitting the price into all the components

Every part of a price should be stored separately. If not, there’s no way to reproduce the components if you need to later on. So save the VAT amount, the discount amount, the base price, and the total all separately. Big chance there are gonna be more price components in your app in the future.

4. Using foreign keys in the ‘orders’ table

One of my dumbest mistakes. I had an ‘orders’ table where all the orders of an e-commerce store were placed. Unfortunately, it had a reference to the actual products which I got the product price from. Everything was fine until one of the product prices changed and older orders were affected by it😅

I’ve made many mistakes even though I have been developing applications for years. But without resistance, there’s no growth, so I tend to share my mistakes so you might prevent them.

I’m planning on writing an ebook on developing applications where you work with money. If you’re interested you might wanna subscribe to get free access to the first chapter.

Subscribe here

Discussion (2)

Collapse
mottavianidev profile image
mottaviani-dev

Decimal(15,2) is never enought precision since the database is going to potentially apply unwanted rounding. 17,4 or 18,5 makes you sleep at night when you will apply rounding/flooring yourself by your business rules.

Do not write ebooks about what you dont know.

Moneyphp is your best mate, stick to it.

(You may also need a product_snapshot table alongside a product_snapshot_attributes)

Collapse
lptn profile image
Alies Lapatsin

Using foreign keys in the ‘orders’ table

Not sure this is an issue. E.g. if you have "prices" table - all "prices" records should be immutable, the same for other related tables