Subject Re: [firebird-support] How to store value in different currency ?
Author Magnus Titho
Am 09.04.2012 19:44, schrieb nathanelrick:
> How do you handle this ? actually i store all the value in EUR in the database with a field EUR2CurrencyRate ... and every week i update the value in EUR with the new exchange rate. it's too much intensive as i need to scan every week all the database to update all the value that was not filled in EUR
First, don't stor ethe exchange rate in every record. Build a currency
table with the exchange rates and use this table as a foreign key in
your other table

ForeignValue, ExchangeRate
ForeignValue, Currency +
Currency, ExchangeRate

Therefore your only have to update the (very short) currency table.

Next, if you want to find mathcing records, you'll be probably better
of, if you first calculate the value in foreign currency and then look
for that value, e.g.

select * from Table1
where ((currency = 1) and (foreignvalue = 123.45)) or ((currency = 2)
and (foreignvalue = 23.45)) ...