Ideal data type for currency rate in MySQL

Question

What should I ideally use to store currency rate information in my MySQL database? Should I use:

• double
• decimal like (10,4)
• float or
• something else?

Currency rates are usually decimal numbers like 1.2362.

Answer 1

How precise do you need to be? I ask because the problem may go beyond the storage format of the conversion factor. For example, perhaps the rules might be to keep the factor to N decimal places, perform the multiplication (or division) to M places, then round. And the "round" might be "financial" -- 0.5 always rounds up, instead of IEEE-754, "round to nearest even".

Assuming you can afford to be off by one unit in the last place (cents or centimes or whatever), I would simply use DOUBLE.

I assume you won't be storing more than, say, 100 conversion factors, so the fact that DOUBLE occupies 8 bytes will not be an issue. If you are recording the conversion factors for every minute for the past 10 years, I would rethink this decision.

FLOAT gives you 6-7 significant digits (and takes 4 bytes); DOUBLE gives about 16. 12345 and 123.45 and 0.00012345 each have 5 "significant" digits.

For currency, itself, I think the maximum number of decimal places for any currency today is 4. That is, DECIMAL(nn, 4) should suffice for storing money.

However, that does not provide the necessary rounding.

ROUND(currency1 * factor, decimals)

where decimals is 2 for a target currency of USD and Euros (and many other currencies). As I pointed out above, this may differ from what a bank would compute by as much as one cent.

(Would you call this "opinion based"? Or something better?)