php - Mysql database design for curency exchange website application -

i building website use openexchangerates.org's api.

the api returns json response looks like:

{   "timestamp": 1391191261,   "base": "usd",   "rates": {     "aed": 3.67328,     "afn": 56.4876,     "all": 103.8916,     "amd": 411.612997,     /* 165 currencies */     "yer": 215.057,     "zar": 11.14973,     "zmk": 5253.075255,     "zmw": 5.579003,     "zwl": 322.355006   } } 

now thinking correct table database structure value. displays exchange rates years ago. not use rates within month range. how handle type of situation display curency comparations different days , on?

table:

curency \ rate    \ date  aed     \ 3.67328 \ 31-01-2014 afn     \ 56.4876 \ 31-01-2014  \* , on date... older rates past dates *\  aed     \ 3.12367 \ 30-01-2014 afn     \ 55.2345 \ 30-01-2014 

is database design? else should do?

you should use table this:

id | currency | rate | start-date 

when you're searching currency, have entry correct date (latest start-date, still earlier date you're searching for).

select: select * table date < searchdate order date desc limit 1;

if rate changes, have add new row, current sysdate.

select: insert table values (id, 'eur', '1.4', sysdate);

so yes, think table ideal.

but: problem can appear, if deleting entries - , suddenly, there no rows "usd" anymore, , database forgot us-dollar.

let's take @ example - know 2 currencies, , stored in table:

id | currency | rate | start-date ---------------------------------  1    usd        1      1.1.1970  2    eur        2      1.1.1970  3    eur        3      5.1.1970 

if want delete first entry (usd), can it. problem appears is, don't have entry usd anymore - , our database stores rates 1 currency.

in case, might want use 2 tables foreign-key.

see example:

table 1:

id | currency 

table 2:

id | fk_currency | rate | start-date 

the first table stores currencies, avaiable. , second table stores rate. column fk_currency foreign key on table1, , contains id of currency.

if delete rate table2, still have currency stored in other table. database didn't forget it.