Design and store a Currency Exchange Rates Cross Table

Question

I need to store the values of a currency exchange rates cross table (e.g. http://www.exchangerates.org.uk/currency/currency-exchange-rates-table.html) using an RDBMS, MySql in my case.

The user is going to update the figures daily and the system will store the different versions.

I was wondering how you would design the tables or if you want the models.

The simplest way is, of course, by using a table with from, to and values columns

from: char(3)
to: char(3)
value: decimal(6,4)
inverse_value: decimal(6,4)

but I would love to know if there are other (better) solutions.

Thanks a lot.

EDIT

I apologise if it was not clear but I'm particularly interested in performances and scalability.

Keeping the value/inverse_value structure and given 90 currencies, the Currency Exchange Cross Table would need 4,050 records per day.

If a new version is created every day, in one year there would be 1,478,250 records and the queries could start to suffer.

I've implemented the table and it's working just fine, rendering the cross table is quite fast and I'm happy with it.

I was just wondering if there was a better way to implement this.

Answer 1

That looks like a good start, I would also add a date field (rather than a datetime field if you are only updating these values daily). So maybe something like this:

currency_code_from: char(3)
currency_code_to: char(3)
conversion_value: decimal(6,4)
inverse_conversion_value: decimal(6,4)
effective_date: date()

I am not sure what the various ways you are planning to query the table are, as this will dictate your index requirements, but I would probably use a compound primary key across the currency_code_from, currency_code_to, and effective_date fields, then add any indexes as necessary for your specific queries.

You then may want to have an additional table to relate to that stores the name of the currency and the currency symbol if needed for display (also perhaps link to country flag image if you want to use that)

currency_code: char(3)
currency_name: varchar(50)
currency_symbol: char(3)
currency_image: varchar(100)

Primary key on this table would be currency_code.

Answer 2

Marco, You would probably need a date field as well or a boolean isCurrent so you can select the latest ccy conversion.

Are you wanting to have buy and sell figures? Normally you make sure there is a bit of lead way between the buying and selling of ccy to make sure the organisation doesn't lose. This is called a sensible business approach by some and gouging by others.

If these figures are manually entered, make sure you look at the previous figure for the currency and if there is a discrepancy of > 3% then warn the user.

The only other issue is when exchange rates fluctuate widely over the day. Do you want to be stuck with just one conversion pre the declaration of war?

Appears that you are on the right track.