Normalized Tables & SELECT [was: Find "smallest common year"]

>> 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
____________________________________________________________________