[dev] Best strategy to store money values

[Ralf Lang]

Am Mittwoch, 17. August 2011, 02:12:07 schrieb Luis Felipe 
Marzagao/Andamentos:

> >> I have never developed any app involving money values and
> >> calculations, so I could use some advice concerning the best
> >> strategy/practice before I invest time going the wrong direction.
> >> 
> >> Any pointers are appreciated.
> > 
> > The form type should be "number", the column type "float".
> > 
> > Jan.
> 
> Thanks. I still have to do some manually conversion, because the
> "number" form type only validates and returns the number wih your local
> decimal point (in my case the comma ',', not the point '.'), which is
> not accepted by mysql float type. It seems mysql expects to receive a
> pointed decimal number (like 500.10), otherwise it will loose the digits
> after the decimal point. But this is something manageable.
> 
> Let me enjoy the opportunity to ask how do I tell migrations create the
> column type 'float' with the options (12,2), twelve digits, two being
> after the decimal point. I can't find any reference on how to pass the
> correct parameters to $t->column( .... array(????));

You probably know that, I just want to remind:
The float type is not suitable if you want to use the price/money value field 
for "equals" matches.

Simplified example:

mysql> CREATE TABLE items (money_value FLOAT);
mysql> INSERT INTO items VALUES (0.99);
mysql> select money_value from items;
+-------------+
| money_value |
+-------------+
|        0.99 | 
|        0.66 | 
|        0.33 | 
+-------------+

BUT
mysql> select money_value from items where money_value=0.33;
Empty set (0.00 sec)

mysql> select money_value from items where money_value=.66;
Empty set (0.00 sec)

And so on. 

Workaround
mysql> select money_value from items where money_value < .66 + 0.00001 and 
money_value > 0.66 - 0.000001;
+-------------+
| money_value |
+-------------+
|        0.66 | 
+-------------+
1 row in set (0.00 sec)

If you need exact database matches on money values, use a fixed-point type.

-- 
Ralf Lang
Linux Consultant / Developer

B1 Systems GmbH
Osterfeldstraße 7 / 85088 Vohburg / http://www.b1-systems.de
GF: Ralph Dehner / Unternehmenssitz: Vohburg / AG: Ingolstadt,HRB 3537