Multi-Currency in RDBMS: Integer, Decimal, Float.
From: linevoltage_halogen <tropicalfruitdrops_at_yahoo.com>
Date: 29 Nov 2004 07:29:30 -0800
Message-ID: <bee7ac87.0411290729.19177c6d_at_posting.google.com>
Greetings All, we are currently in the exploratory phase of building a financial app that has the requirement of globalization support. Although there are many questions to answer I have a specific question around the actual physical storage of monetary values in the DB. Specifically, I am concerned about rounding errors and the storage of mixed monetary types. I recently read a great paper put out by IBM on decimal arithmetic which essentially says that all monetary values should be stored as integers, that is to say that if you have $34.12 you would store it in pennies as 3412. Further, " ...most commercial data are held, and calculations are carried out, using decimal arithmetic, almost invariably operation on numbers held as an integer and a scaling power of ten." However, the particular issue I have is if you want to store U.S. dollars and Turkish Lira in the database at the same time. For those of you who are not up on your exchange rates there are about 1.4 million Turkish Lira per U.S. Dollar. If for example the input currency is in USD and say it is 1,000,000.00 this will be stored as 100000000, its equivalent in Turkish Lira will be 140000000000000. Further, if the input currency is in Turkish Lira then I would end up with a small fraction representing USD, 1 Turkish Lira = 0.0000006942 USD, unless I have large amounts of Lira my USD amount will always be a fractional amount. The question here is what should be multiplicative factor when converting the values for storage in the database. One note here is that this app will have the concept of a base amount and a transactional amount that must both be stored in the database and these two amounts could in fact be USD and Turkish Lira.
Date: 29 Nov 2004 07:29:30 -0800
Message-ID: <bee7ac87.0411290729.19177c6d_at_posting.google.com>
Greetings All, we are currently in the exploratory phase of building a financial app that has the requirement of globalization support. Although there are many questions to answer I have a specific question around the actual physical storage of monetary values in the DB. Specifically, I am concerned about rounding errors and the storage of mixed monetary types. I recently read a great paper put out by IBM on decimal arithmetic which essentially says that all monetary values should be stored as integers, that is to say that if you have $34.12 you would store it in pennies as 3412. Further, " ...most commercial data are held, and calculations are carried out, using decimal arithmetic, almost invariably operation on numbers held as an integer and a scaling power of ten." However, the particular issue I have is if you want to store U.S. dollars and Turkish Lira in the database at the same time. For those of you who are not up on your exchange rates there are about 1.4 million Turkish Lira per U.S. Dollar. If for example the input currency is in USD and say it is 1,000,000.00 this will be stored as 100000000, its equivalent in Turkish Lira will be 140000000000000. Further, if the input currency is in Turkish Lira then I would end up with a small fraction representing USD, 1 Turkish Lira = 0.0000006942 USD, unless I have large amounts of Lira my USD amount will always be a fractional amount. The question here is what should be multiplicative factor when converting the values for storage in the database. One note here is that this app will have the concept of a base amount and a transactional amount that must both be stored in the database and these two amounts could in fact be USD and Turkish Lira.
As a side note my gut tells me to only store the data that was actually inputted to the app and instead of converting it at that time only store the relevant exchange rates in say the invoice detail record. This will give the app the flexibility of letting the users of the app define the rounding strategy they want to use for their particular organization.
With all that confusion stated I hope someone can sift through it and get the gist of what it is I am trying to say and get back to me with some professional insight.
Regards, TFD Received on Mon Nov 29 2004 - 16:29:30 CET