Two years ago, I was assigned to develop a platform where user can deposit money into e-wallet and make payment for game, telco, bill and many other services from that e-wallet. Instead of target to direct end users, the platform are specially catered for small business (Internet cafe, convenience store etc) owners, also called as a retailer, who charged cash from customer and perform the payment for them. The difference between the cash charged from customer and the money that is deducted from his e-wallet will be the profit or commission that a retailer can earn.
Fixed-point V.S. Floating-point
The very first function in this system is to track how much money user has deposited in his e-wallet, i.e, we need to keep a number for the balance of e-wallet. What's more, the number may not be integral, i.e, it may have fraction. Most of programmers are familiar with the floating-point data type that is the formulaic representation that approximates a real number so as to support a trade-off between range and precision. However, there is also a fixed-point number representation for numbers that have a fixed number of digits after (and sometimes also before) the radix point. Fixed-point number is usually the preferred choice when storing critical information like monetary data where exact precision must be preserved. Most of programming languages and DBMS offer support for both data types. In Python, the following statement defines a decimal number with a precision of 2 digits after decimal point.
>>> from decimal import Decimal >>> balance = Decimal('2.78')
One caveat here is if we omit the quota mark:
>>> balance = Decimal(2.78) >>> print balance Decimal('2.79000000000000003552713678800500929355621337890625')
The result may be different on different platforms and CPU architectures.
decimal to store exact numeric data types. Here we choose decimal to store the balance:
CREATE TABLE `retailer_tab` ( ... `balance` DECIMAL(20,2) NOT NULL ...
Django's ORM model translate the decimal type in Python to SQL with single quotation marks. However, in MySQL 5.x the following operation will trigger a warning, which caused exception in Django ORM:
mysql> UPDATE retailer SET balance = 10.00; mysql> UPDATE retailer SET balance = balance - '1.79'; Warning: Data truncated for column 'balance' at row 1
One workaround is using floating point in Django ORM so that the translated SQL becomes:
mysql> UPDATE retailer SET balance = balance - 1.79;
but again, this involves floating point conversion and may lose precision.