Here's a different approach for which recomputing the calculated_price is just an optimization, as opposed to being strictly necessary.
Suppose that in the currencies tables, you add another column, last_rate, which contains the exchange rate at the time the calculated_price was last updated, no matter when this happened.
To quickly retrieve a set of products with a price point between, say, 50 USD and 100 USD that include the desired results, you could do something like that:
SELECT * FROM products
WHERE calculated_price > 50.0/(:last_rate*
(SELECT coalesce(max(value/last_rate),1) FROM currencies
WHERE value>last_rate))
AND calculated_price < 100.0/ (:last_rate*
(SELECT coalesce(min(value/last_rate),1) FROM currencies
WHERE value<last_rate))
where :last_rate contains the EUR/USD exchange rate at the time of the last update. The idea is to increase the interval to take into account the maximum variation of every currency. The increase factors for both ends of the interval are constant between rates updates, so they could be pre-computed.
Since the rates change only slightly over short periods of time, the above query is likely to give a close approximation of the final result. To get the final result, let's filter out the products for which the prices have slipped out of the bounds due to the changes in rates since the last update of calculated_price:
WITH p AS (
SELECT * FROM products
WHERE calculated_price > 50.0/(:last_rate*
(SELECT coalesce(max(value/last_rate),1) FROM currencies
WHERE value>last_rate))
AND calculated_price < 100.0/ (:last_rate*
(SELECT coalesce(min(value/last_rate),1) FROM currencies
WHERE value<last_rate))
)
SELECT price,c.value FROM p join currencies c on (p.currency=c.id)
WHERE price/c.value>50/:current_rate
AND price/c.value<100/:current_rate;
where :current_rate is the more up-to-date rate with EUR for the money choosen by the user.
The efficiency comes from the fact that the range of rates is supposed to be small, the values being close together.
From a database (and client) point of view, you need to do a few things.
The first thing you need to consider is that you have to use integers for everything and have a number of implied decimals for each currency (eg: $2.00 is actually 200 with 2 implied decimals. The $2.00 part is a client formatting issue and a database maths issue.). Floating point never comes into it - you don't want to go near the mathematical implications involved in using floating point.
My advice: Have a currency table with the currency_id (PK), currency_name and decimals fields. Any other table then just needs a currency_id and amount column to relate to the original table. Formatting is then just a join between the two. Maths between any tables with the same currency is then just straight integers.
Maybe add a few more columns to the currency table for leading and trailing symbols (eg: '$' for prefix, '.' for decimal spacer) for formatting.
Best Answer
There is no answer to that - it depends. If you are a smaller outfit that with locality (like a dozen shops in the USA) then all you really care about is the price in USD. If you are an international retailer with websites in many countries with local pricing, you need to keep likely even multiple prices per product in different currencies.
It should not be there. it assumes not only one currency (which wold be the base currency the system runs in) but also no rebate system at all. Pricing, except for really simplistic systems, is a separate table.
Suggested reading: Data Model REssouce Book, Volume 1 - whole chapter on storing prices.
If yo ustore it like this, it is either base currency (stored somewhere central) or yes, you add a currency id field to reference the currency. I suggest using the ISO currency code for that, and adding negative numbers if needed when no official code is assigned.