4

I'm working on a small project and I'm have arrived to the point where I let a user chose in which currency to display items.

I'm working with Java and my first thought was that I would use some kind of API to pull the rates and post process (loop) after I get the the items from the DB. But I'd rather not have to unnecessarily loop if I can directly get the right price form the query.

So I think I'm going to store all the exchanges rate in the DB and have my query do the conversation job here is my first draft:

 CREATE TABLE currency (
      code VARCHAR(3) NOT NULL,
      symbol VARCHAR(3) NOT NULL,
      display_name VARCHAR(4) NOT NULL,
      PRIMARY KEY (code),
      UNIQUE KEY uq_ccode (code)
    );

CREATE TABLE exchange_rate (
  currency_code_from VARCHAR(3) NOT NULL,
  currency_code_to VARCHAR(3) NOT NULL,
  rate numeric(15,4) NOT NULL,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (currency_code_from, currency_code_to),
  FOREIGN KEY (currency_code_from) REFERENCES currency(code) ON DELETE CASCADE,
  FOREIGN KEY (currency_code_to) REFERENCES currency(code) ON DELETE CASCADE
);

I would then write a query like:

select price as originalPrice, (price * rate) as TargetPrice FROM .....
JOIN exchange_rate er ON er.currency_code_from=(the currency of the item) AND er.currency_code_to=(the user currency)......

Note that on the above tables I would have to have every single combination

GBPEUR
EURGBP
GBPGBP (well i guess i can probably avoid this one if a slighty amend the query to detect this scenario)

Plus.. I would have to have some kind of cron job to daily update this table.

Now I'm far to be a banker nor a SQL genius so I would like some input from people that have already written such tables/query and that have expertise in this kind of stuff

Am I fare off with my design? Would I be better off post processing the results and do the conversation in java with rates coming from an API?

Improve this question
1
  • 2
    From my experience (this being something I use daily), your design is fine. There's nothing wrong with storing a combination for every single currency. We have 17.5k combinations, updated daily and, as long as you index properly, it's the better way to go.
    – Mark Sinkinson
    Dec 31, 2015 at 12:27

1 Answer 1

Reset to default
1

How are your underlying item prices stored?

If, for example, your items are all priced/stored in EUR, then there is no need for an NxN (duplicative) currency matrix.

In this case, exchange_rate (against EUR or your base currency) can simply be a field in the currency table (vs an independent table).

Then all of your conversions for display (which are all then simply EUR->XXX) can be calculated with a single JOIN.

Improve this answer
6
  • I unfortunately cannot do this (which would he been the way to go if there was a base currency). Basically every item can be in any currency. (Think as AirBnB. depending on your country where you list your room the currency is different, and as a visitor, you can chose in which currency you want the room to be displayed in)
    – Alexis
    Dec 30, 2015 at 22:48
  • 1
    This is the only way to prevent the combinatorial explosion. If you don't have a base currency, pick one. Then for whichever price you need to display, there is always one join.
    – Jonny
    Dec 30, 2015 at 23:47
  • 1
    Let's say a pick EUR as base currency. Does that mean that when I store an item I will have to convert it to EUR first (from whatever currency the seller decided to put its price in) ? By doing this I would have to frequently update the items price instead no ? (if today a seller put a 100GBP (135.92EUR) for a 1.36 rate, and tomorrow the rate changes to 1.40 the price if the DB (135.98) is now wrong
    – Alexis
    Dec 31, 2015 at 11:42
  • 1
    It sounds like the best practice is to store the price in the seller's currency, and convert source->base->target at the point you need to display to/transact with buyer. Guess that derails the single join idea... Read more: yacoset.com/how-to-handle-currency-conversions
    – Peter Dixon-Moses
    Dec 31, 2015 at 13:08
  • 1
    Best performance using the DB alone sounds like the NxN conversion table, though on the app side you might realize some caching benefits by normalizing everything to a base currency in your query, caching, and then post-processing to target currency.
    – Peter Dixon-Moses
    Dec 31, 2015 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.