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
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
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?