Multi currency information system, SQL inaccuracy

Question

I am working on development of information system which must handle multi currencies ( USD, EUR, JPY ). Let's say, user can add products to system. User adds product #1 on 13 Feb 2017, price 2000, currency JPY. User adds product #2 on 14 Feb 2017, price 25, currency USD.

Later (15 Feb 2017) user can view and filter his products, let's say user wants to view products in USD and there is a price range filter.

Goal:

• user sets some price range and i have to find these products in database properly

My current approach is:

• i have decided, that EUR is my unified currency
• when user adds product - convert each product price to EUR using today exchange rate
• in database, original price and currency is stored (from user input), converted price(EUR) is stored
• price range in filter is initialized by today rate, so 2000 JPY is converted to USD as lower limit (17.5 USD), 20 USD as upper limit

Problems:

• user sets limit from 17.5 USD do 20 USD
• I convert 17.5 USD to a EUR_value_1, 20 USD to a EUR_value_2
• select to database where price_unified between EUR_value_1 and EUR_value_2
• values does not match products in database, because used rate can be higher / lower (every day) so values are different

My questions are:

• what is the right way to store these products, prices, currencies in database ? Should i store all currency values ?
• how to handle frontend behavior as price range filter in some currency but still be able to find products in database properly ?
• should i create a cron to re-convert all prices by today rate ?

Answer 1

Currency rate tables are common in international finance applications. Usually, everything is referenced as a ratio against one currency, such as USD. Only one entry is needed for each currency per day, usually the closing rate of the previous day -- except for the base currency, which is always 1. Like this:

Effective  Code Factor
2015-05-02 GBP  0.662251656 --based on today's rate of 1.51 GBP->USD

To go from USD to GBP: USD * factor = GBP
To go from GBP to USD: GBP / factor = USD

There is a slide presentation that shows such a table design here. The currency discussion starts on slide/page 12, the query on page 16. The table design is simple:

create table XRates(
    Code       char( 3 ) not null,
    Effective  date not null,
    Factor     decimal( 12, 9 ) not null
    constraint PK_XRates primary key( Code, Effective )
);

A couple of nice features about the design is that you maintain historical rates in the same table as the current rate and daily updates are not necessary. An entry is made only when the rate changes -- or when the rate changes enough to trigger an update. The query returns the rate that was in effect on the given date, even if that rate was established by a weeks-old entry (not likely in today's currency markets).

You would, of course, set your base currency to the Euro, but the table design and query would not change.

So you need only store the price and the currency of a product. The price in Euro of a product that was priced at ¥2000 can easily be converted according to the conversion rate in effect, say, the day the order was placed.

Answer 2

Store the original price in the original currency. Your last question is a business decision, not a technical one so that do not expect an answer here.

Option 1

To handle it the flexible way,I would suggest you create a separate table structure to store the exchange rates. Create a function that would return a table structure with the exchange rates per available currency. This way you can easily swap from using per day rates or whatever - you will isolate that logic with minimal performance price.

Next step is to create a SP to query for the results. Special point here is that you should modify the boundaries that you search in instead of the actual value so that you can still use the indexes in the table.You will use the result of the function to generate a table that would contain the currency and the adjusted boundaries for that currency. You will join on that table.

I can post an example if you need one - bump the answer or write a comment if it is so.

Option 2

If you do not need flexibility or you would rarely change the exchange rates - again, store the price in the original currency but also compute and store the price in the unified currency. You can do it in a separate table or a in new column if you do not care about normalization that much. Again, make searches by modifying the boundaries and comparing towards the pre-computed, unified price to make use of indexes