I want to make it possible for user to search products within a price range. User should be able to use any currency (USD, EUR, GBP, JPY, ...), no matter what currency is set by the product. So, the product price is 200USD and, if the user searches the products that costs 100EUR - 200EUR, he still may find it. How to make it fast and effective?
Here is what I have done until now. I store the price
, currency code
and the calculated_price
that is the price in Euros (EUR) that is the default currency.
CREATE TABLE "products" (
"id" serial,
"price" numeric NOT NULL,
"currency" char(3),
"calculated_price" numeric NOT NULL,
CONSTRAINT "products_id_pkey" PRIMARY KEY ("id")
);
CREATE TABLE "currencies" (
"id" char(3) NOT NULL,
"modified" timestamp NOT NULL,
"is_default" boolean NOT NULL DEFAULT 'f',
"value" numeric NOT NULL, -- ratio additional to the default currency
CONSTRAINT "currencies_id_pkey" PRIMARY KEY ("id")
);
INSERT INTO "currencies" (id, modified, is_default, value)
VALUES
('EUR', '2012-05-17 11:38:45', 't', 1.0),
('USD', '2012-05-17 11:38:45', 'f', '1.2724'),
('GBP', '2012-05-17 11:38:45', 'f', '0.8005');
INSERT INTO "products" (price, currency, calculated_price)
SELECT 200.0 AS price, 'USD' AS currency, (200.0 / value) AS calculated_price
FROM "currencies" WHERE id = 'USD';
If user is searching with other currency, let's say USD, we calculate the price in EUR and search the calculated_price
column.
SELECT * FROM "products" WHERE calculated_price > 100.0 AND calculated_price < 200.0;
In this way we can compare prices very fast, because we don't need to calculate the actual price for every row, because it's calculated once.
Bad thing is that at least every day we have to re-calculate the default_price
for all rows, because the currency rates has been changed.
Is there a better way to do deal with this?
Isn't there any other clever solution? Maybe some mathematical formula? I have an idea that the calculated_price
is a ratio against some variable X
and, when the currency changes, we update only that variable X
, not the calculated_price
, so we even don't need to update anything (rows)... Maybe some mathematician can solve it like this?