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?