Re: Storing & Manipulating Currency in the database.
From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 12/01/04
- Next message: Aaron [SQL Server MVP]: "Re: Storing SQL data from dotnet....should I use a trigger?"
- Previous message: Scott Simons: "RE: Storing SQL data from dotnet....should I use a trigger?"
- In reply to: linevoltage_halogen: "Storing & Manipulating Currency in the database."
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 1 Dec 2004 09:39:22 -0500
I worked with an app with similar requirements and the design that was
chosen was to store all amounts and the currency they were inserted as, with
a FK to a currencies table containing exchange rates, symbols (for the UI to
use), and abbreviations (e.g. 'USD' = US Dollars), also for the UI. In this
case, all exchange rates were correlated to USD (so USD was always 1.0) and
were updated on a daily basis. Any calculations between non-USD, therefore,
had to be converted into USD first. That did work quite well, but Turkish
Lira was not a supported currency ;)
The issue, by the way, with storing data in a currency other than that which
was initially entered is that as the exchange rates change that value can
change. So I would definitely keep things in the format they were in
originally.
-- Adam Machanic SQL Server MVP http://www.sqljunkies.com/weblog/amachanic -- "linevoltage_halogen" <tropicalfruitdrops@yahoo.com> wrote in message news:bee7ac87.0412010610.7cbd68eb@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
- Next message: Aaron [SQL Server MVP]: "Re: Storing SQL data from dotnet....should I use a trigger?"
- Previous message: Scott Simons: "RE: Storing SQL data from dotnet....should I use a trigger?"
- In reply to: linevoltage_halogen: "Storing & Manipulating Currency in the database."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
