What data type is best for storing currency? I’m trying to come up
with a reliable cross-database solution for rails that is not too
complicated. Since I’m going to have currency fields all over the
place, I’m not so sure that using two integer fields for each amount
would qualify as uncomplicated. I’d like to be able to use database
functions like SUM to retrieve totals, so I don’t want to have to
split this information off or make it so normalized that queries
become heinous.
Sorry if this issue has been discussed on the rails forum before but I
couldn’t find much when I searched the archives.
Carl
On 1/16/06, Carl Y. [email protected] wrote:
couldn’t find much when I searched the archives.
I’ve had good luck storing things as integer/number fields representing
cents.
$1.00 = 100, etc. Every currency has a base unit that can’t be
divided further, and if you defer calculations until the last moment,
you don’t have to worry about losing precision.
On the other hand, I have no idea how serious financial systems deal
with ‘fictional’ amounts, like $0.00071212312, so don’t go around
implementing stock markets based on this email.
On Jan 17, 2006, at 12:22 , Carl Y. wrote:
What data type is best for storing currency? I’m trying to come up
with a reliable cross-database solution for rails that is not too
complicated. Since I’m going to have currency fields all over the
place, I’m not so sure that using two integer fields for each amount
would qualify as uncomplicated.
Two ways I’ve heard of are:
-
Use a numeric field with the appropriate scale. However, I don’t
know if this is the best solution for Rails, as I seem to remember
that Rails uses floats for numeric-defined fields. (Someone please
correct me if I’m wronge.) Rounding may cause problems when you’re
dealing with currency.
-
Use an integer field and store the scaling information elsewhere,
performing formating on the fly. For example, 10 dollars or 10 euros
would be stored as 1000, with scale of 2. 1000 yen would be stored as
1000 with scale of 1. The scale information could be stored with the
currency information (which I’d guess where you’re going to do your
other formatting, such as currency symbol).
I’d like to be able to use database
functions like SUM to retrieve totals, so I don’t want to have to
split this information off or make it so normalized that queries
become heinous.
Either 1 or 2 should let you use database aggregate functions.
Hope this helps.
Michael G.
grzm myrealbox com