Storing & Manipulating Currency in the database.
From: linevoltage_halogen (tropicalfruitdrops_at_yahoo.com)
Date: 12/01/04
- Next message: Rick Sawtell: "Re: Upgrade to SQL 2000"
- Previous message: Jacco Schalkwijk: "Re: Backups running concurrently with db maintenance"
- Next in thread: Adam Machanic: "Re: Storing & Manipulating Currency in the database."
- Reply: Adam Machanic: "Re: Storing & Manipulating Currency in the database."
- Messages sorted by: [ date ] [ thread ]
Date: 1 Dec 2004 06:10:59 -0800
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: Rick Sawtell: "Re: Upgrade to SQL 2000"
- Previous message: Jacco Schalkwijk: "Re: Backups running concurrently with db maintenance"
- Next in thread: Adam Machanic: "Re: Storing & Manipulating Currency in the database."
- Reply: Adam Machanic: "Re: Storing & Manipulating Currency in the database."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
