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.
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?
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.
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.