What would you recommend for say, 500 global national statistical
variables,
500 regional and 500 subregional and 500 global aggregations?
Years being
covered having something between 10 and 60 years for each of these
variables. All available for 240 countries/territories.
I generally approach such problems by putting the data right
(normalized) at the start, then munging the data into summary tables
to handle the problems you're seeing now.
I find it far easier to maintain normalized tables that produced
non-normalized ones (for things like data warehousing) than it is to
maintain non-normalized tables and trying to produce normalized data
from that.
Ok, I do understand that.
So, instead of the earlier mentioned database design, I would have
something like this:
- one table for the country names/ids/etc. (Afghanistan, 1;
Albania, 2....)
- one table for the variable names/ids/etc. (GDP, 1; Population,
2; Fish Catch, 3;....)
- one table for the years names/ids/etc. (1970, 1; 1971, 2; 1973,
3; ....)
and
- one table for all "statistical data" with four fields -
id_variable, id_country, id_year, and the actual value
You say
I find it far easier to maintain normalized tables that produced
non-normalized ones (for things like data warehousing) than it is to
maintain non-normalized tables and trying to produce normalized data
from that.
It seems to me more difficult now to produce a non-normalized output
based on the normalized table. How would look a query like, if I need
now to SELECT, say 100 countries and 20 years? Something like this
(simplified and without joins):
SELECT
value,
id.year
FROM
main_table
WHERE
year = '1970' OR
year = '1971' OR
....
country_name = 'Afghanistan' OR
country_name = 'Albania' OR
...
Actually, last time we came up with SubSelects for each year. So,
does this make sense?
Thanks a lot for your help!
Stef
____________________________________________________________________
Stefan Schwarzer
Lean Back and Relax - Enjoy some Nature Photography:
http://photoblog.la-famille-schwarzer.de
Appetite for Global Data? UNEP GEO Data Portal:
http://geodata.grid.unep.ch
____________________________________________________________________
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
