David Garamond wrote:
> I plan to store amount in a column (NUMERIC) and currency id in another
> (CHAR(3)). Plus another column for amount in 'standard' currency (e.g.
> USD; all addition/sum will be done to this column).
If your system really is going to handle multiple, simultaneous currencies,
beware of constant changes in the exchange rate between them. Probably
you'll be better by never storing anything in a 'standard currency' column
and doing instead all math on the fly, referring to a separate
'exchange_rates' table when needed (i.e. always :-). Of course, all of this
has nothing to do with the technical solution but instead with the bussiness
rules the application must follow, so they must be incorporated early at the
spec level. In the end, probably an accountant will be the most qualified
one to define these things.
With regard to precision, it is common in certain applications the need to
handle very small amounts, especially when used as factors of a larger
calculation. I've even seen once some rates defined in hundredths of cents!
hth
cl.
