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.
Top comments (3)
Thanks for sharing this information and your experience.
Building a financial app is for sure not easy, and it requires some skills and knowledge.
I'm only at the beginning of my path, and reading such articles is very useful. Becoming a specialist in fintech requires time, effort, and dedication; that's why I try to do my best and dedicate some time every day to learning. I was also looking for companies offering their services in that field. I've already found this company called DashDevs, and it seems to be a good one to choose when it comes to the development of different fintech products. The cases are great, and I think that when I have an idea but not enough skills to recreate it, I'd for sure use the help of the professionals of that company.
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)
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