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?

Md Haidar Ali Khan
  • 6,523
  • 9
  • 40
  • 62
Alexis
  • 289
  • 2
  • 6
  • 12

1 Answers1

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.

Peter Dixon-Moses
  • 141
  • 2