I would like to create a DB of all my data. I realise that the most important aspect is the design and this is where Im stumped.
The desired outcome is that I should be able to select Continent and then have it show the relevant countries (All also being a continent) and then select the variable (option to select several at once) and then a time series. Considering I have 196 countries and most series span from 1960-2020 including forecasts it all seems a bit much. I tried breaking the data down as far as possible so that I could have it as uniquely as possible in its own table but even that is flawed.
An example of the type of fields I started of with:
1. Country detail table:
3. Region (this will have multiple values as sometimes I would like to bring up the country lists by trade blocs or other groupings rather that continent)
2. GDP, constant pricing, National currency 2012 table
Country (related to the field from table 1)
End date (since every country would have data every year I have made a new table for each year and the end date field would be the same for all the entries in the table
variable value (the GDP value)
variable series (since there are many series, several GDP series alone)
Very new (opened Access for the first time this morning) and very lost