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?