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 Youngblood <carl@youngbloods.org> 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. 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. >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 Youngblood 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 Glaesemann grzm myrealbox com