Query multiple currencies

Question

I need store multiple currencies on my database... Here's the problem:

Example Tables:

[ Products ]
id (INT, PK)
name (VARCHAR)
price (DECIMAL)
currency (INT, FK)

[ Currencies ]
id (INT, PK)
name (VARCHAR)
conversion (DECIMAL) # To U$

I'll store the product price with the currency selected by the user...

Later I need to search the products using a price interval like "Search products > with price from U$ 50 to U$ 100" and I need the system convert these values "on the fly" to run the SQL Query and filter the products.

And I really don't know how to make this query... :/

Maybe something like this?

SELECT p.`name`, p.`price` * c.`conversion` AS price
FROM `products` AS p
INNER JOIN `conversion` AS c
   ON p.`currency` = c.`id`
WHERE price >= 50 AND price <= 100
LIMIT 10

Answer 1

SELECT P.name, P.price local_price, P.price * C.conversion usd_price
FROM   Products P
INNER JOIN Currencies C
ON     C.id = P.currency

And then you can add a condition like

WHERE (P.price * C.conversion) > 50

for prices higher than $50.

EDIT

In case you don't have one, it's a good idea to have a currency record for USD (where the conversion value is 1.0), so that conversions to USD are not exceptions.

Answer 2

Off of the top of my head, what i can think of is, that your multiplier for USD will be 1, so you can multiply price with conversion and get the dollar value in the query and use your range on these derviced values. OR if you want to de-normalize, introduce a new column in Products called , "USD_price" and save the value of price * conversion in there, this would simplify and speed things up. However i must point out that the conversion rates will not always be the same, not sure how you are dealing with that.