Money data types


#1

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


#2

On 1/16/06, Carl Y. removed_email_address@domain.invalid 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.


#3

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:

  1. 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.

  2. 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