Re: Multi-Currency in RDBMS: Integer, Decimal, Float.

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Tue, 07 Dec 2004 07:36:09 GMT
Message-ID: <J3dtd.3559$yr1.2074_at_newsread3.news.pas.earthlink.net>


linevoltage_halogen wrote:

> 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

Would you care to give a reference? One IBM site that covers decimal arithmetic is Mike Cowlishaw's site:

http://www2.hursley.ibm.com/decimal/

Which of the papers referenced there are you referring to? If it wasn't one of the papers referenced there, maybe it should be?

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

You should dig up the EU standard on rounding currency conversion that was defined when the Euro was first put in place alongside the classical currencies (bottom paper, LHS of URL above).

Informix databases provide a DECIMAL type - and that can be either a fixed-point number or a floating point number (eg DECIMAL(16,6) or DECIMAL(16)). I'd expect to store conversion factors in a floating point decimal; if a single column had to deal with both Turkish Lira and US Dollars, then I'd probably use a floating point column for that, too, and apply appropriate rounding to different types.

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

My experience (in the dim, distant past) was that there are more different ways of converting currencies than you'd believe possible. You're right; often the best way to handle things is just to store what the user types with full accuracy - and the conversion rates with the full accuracy reported, plus the date or date range and the type of conversion.

No; this isn't identical to storing money quantities in integers. However, the DECIMAL arithmetic is precise within the range of the number of digits used -- 16 significant digits in the example above.

Unless there was a compelling reason to do otherwise, I'd expect to store currencies as a currency code, a (floating point) decimal value, and an associated date. I'd expect to code currency conversions based on 'from', 'to', 'date' or 'date range', 'conversion type' and 'multiplicative conversion factor'. The conversion type might be monthly average based on closing rates on a given exchange, or a daily average, or the rate obtained from a hedging transaction, or any of a large number of possibilities. I'd be open to persuasion that a 'divisive conversion factor' could be stored for the inverse computation in the same entry.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Received on Tue Dec 07 2004 - 08:36:09 CET

Original text of this message