how would one best design a foreign currency value (DBMS-independent)?

the idea that i have in mind would be the following:

--------------

TM_CURRENCY_MASTER table
CurrencyID : integer
CurrencyName : varchar
CurrencyCountry (??) : varchar

T_CURRENCY_MATRIX table
CurrencyID_1 : integer
CurrencyID_2 : integer
ExchangeRate : decimal

--------------

the actual transaction table would then have the following fields (considered as a SET):

...
etc
...
CurrencyID : integer
CurrencyAmount : decimal
...
etc
...


what do you people think?

thanks!!!

sounds good

i like the second table, with

CurrencyID_1 : integer
CurrencyID_2 : integer
ExchangeRate : decimal

this allows an exchange rate for CurrencyID_1 --> CurrencyID_2 that is different from the inverse rate for CurrencyID_2 --> CurrencyID_1

you're probably want a time stamp somewhere, because rates do change frequently

Yes, currency exchange rates change frequently. Worse yet, you can have more than one exchange rate in effect at the same time (for example, the rate from US Dollars to Euros may be different in Cairo and Tokyo at any given point in time! Because of that, for each exchange rate row you want both a begin and an end date (to allow multiple rates to be active at one time).

-PatP

thanks for the suggestion regarding a "start" date, and a "end" date... and of course, the "timestamp"...