It started with a simple question: Can a database handle a customer buying 1.5 kg of sugar?
What followed was a masterclass in software tradeoffs. I went from discovering a critical hardware flaw, to trying two clever workarounds, to finally understanding the gold standard used by major financial systems.
Here is the exact journey of what I learned:
Step 1: The Binary Flaw (Why floats fail)
I discovered that computers think in 0s and 1s (Binary), while humans think in decimals (Base-10). Because of this, standard computer memory cannot cleanly save numbers like 0.1 or 0.2. They turn into endless repeating fractions. If you add 0.1 three times and subtract 0.3, a computer doesn't give you 0.0. It gives you a bizarre micro-error like 5.55111512312578e-17. Over time, these errors compound and break your systems.
Step 2: The Text Bridge (My first workaround)
Since SQLite doesn't have an exact decimal type, my first idea was to save the stock numbers as TEXT (like "1.5") to freeze the characters in place, and use a SQL CAST statement to convert them to numbers when doing math.
I tested this live in a database browser and immediately hit two massive traps. First, text sorts alphabetically, not numerically. A query looking for items with stock "less than 30" will falsely include "100" because the character "1" comes before "3". Second, using CAST forces the database to open every single row and convert the data on the fly, which completely destroys database indexing and slows your app to a crawl.
Step 3: The Rounding Trick (My second workaround)
Then I noticed SQLite has a NUMERIC label and a ROUND() function. It feels like a fix because it cleans up the screen. But under the hood, SQLite's NUMERIC is just a mask - it secretly turns decimals right back into binary floats. Using ROUND() just slaps a band-aid over a broken number after the damage is done. Plus, if you or a teammate forget to write ROUND() in just one single reporting query, those hidden micro-errors leak into your code and cause invisible bugs.
Step 4: The Enterprise Database Illusion
I thought, "Fine, I will just upgrade to an enterprise database like PostgreSQL later, because Postgres has a native, exact Base-10 decimal engine."
But as I dug deeper, I learned that senior developers across the industry avoid using decimal data types altogether, even in advanced databases like Postgres.
Why? Because of four hidden bottlenecks:
Performance: Pure whole numbers (Integers) are processed directly by computer hardware almost instantly. Software-defined decimal types require complex algorithms that calculate math digit-by-digit, creating a massive CPU bottleneck for systems processing millions of transactions.
The API and Code Trap: Even if your database stores a perfect decimal, the moment that number leaves the database via an API or into JavaScript, languages often force it right back into a standard binary float, instantly corrupting it.
Portability: If your code relies on one database's unique decimal engine, your code will break if you ever have to migrate to another system.
Storage: Integers take up a small, fixed amount of space. Complex decimals are variable-length and take up significantly more hard drive space and memory.
The Elegant Ultimate Solution: Integer Scaling
The ultimate breakthrough was realizing that the best way to handle decimals is to eliminate them completely from your data layer.
Instead of tracking 1.5 Kilograms, you multiply by 1,000 and track 1500 Grams as a pure INTEGER. Instead of tracking 14.50 dollars, you track 1450 Cents.
Integers never suffer from binary float distortion. They remain perfect integers inside the database, perfect integers when traveling through a JSON API, and perfect integers inside your frontend code.
The golden rule of engineering is simple: Keep the absolute truth as a whole integer through your entire system, and only insert the decimal point at the very last microsecond right before showing it on the user's screen.
