Design & Shop Currency Rates Cross Table

Question

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

The user will update the numbers daily, and the system will store different versions.

I was wondering how you plan to create tables or whether you want models.

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

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

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

Many thanks.

EDIT

I apologize if this was not clear, but I am especially interested in the capabilities and scalability.

Keeping the value / inverse_value structure and taking into account 90 currencies, a cross-currency exchange table will require 4050 entries per day.

If a new version is created every day, after one year, 1,478,250 entries will be recorded and requests may begin to suffer.

I implemented a table and it works very well, which makes the crosstab pretty fast, and I'm happy with it.

I'm just wondering if there is a better way to implement this.

Answer 1

This seems like a good start, I would also add a date field (and not a datetime field if you only update 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'm not sure what different ways you plan to query the table, as this will determine your index requirements, but I would probably use the composite primary key in currency_code_from , currency_code_to and effective_date , then add any indexes needed for your specific queries.

Then you may want to have an additional table for binding to it, which stores the currency name and currency symbol, if necessary for display (also, possibly, a link to the image of the country flag, if you want to use it)

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

The primary key in this table is currency_code.

Answer 2

Marco, you probably also need a date field or a logical isCurrent so you can select the latest ccy conversion.

Do you want to buy and sell numbers? Usually you make sure that there is a little leading way between buying and selling ccy to ensure that the organization does not lose. Some call it a smart business approach, while others call it rigorous.

If these numbers are entered manually, make sure that you are viewing the previous figure for the currency, and if there is a mismatch> 3%, then warn the user.

The only other problem is that exchange rates fluctuate greatly throughout the day. Do you want to get stuck with just one conversion before declaring war?

It appears that you are on the right track.

Answer 3

I personally would not use "natural" keys. Instead, there is a table of countries / currency entities:

 country ------- country_id : integer not null auto_increment name : varchar(255) abbrev : varchar(255) motto : varchar(255) . . . 

then use the identifiers for those listed in the crosstab:

 currency_exchange ----------------- currency_exchange_id : integer not null auto_increment from_country_id : integer to_country_id : integer value : decimal(10,4) inverse_value : decimal(10,4) 

This allows me to change the name of the country to a character, full name, etc., without changing the definition of the crosstab table. Join the two tables for querying conversion values.

Also, I went with decimal (10.4), and I would recommend looking at the maximum possible. It is not a headache for software updates to limit the size and later discover that you have chosen too small a size. It does not take up enough space to make sense to deal with these errors. The same goes for the cook you use for the name. Varchars are stored efficiently.

Also, isn't value always going to be 1.00? (That is, you do not always convert from 1 to some inverse in another currency?) If so, you can remove the value column from the table.

I personally like to generate identifier values ​​in all tables, so I put it in a crosstab, but some people may suggest that this is not necessary. I often find later, when I decided to leave it, that I would like to add it.

I could see the presence of another table for the currency name:

 currency -------- currency_id : integer not null auto_increment country_id : integer name : varchar(255) symbol : varchar(255)