I really tried it out. I changed my whole database to the "id-country | year | value" - format. And then tried to build my SQL queries. But it was really, really difficult, and sometimes impossible (for me) to get what I wanted.
Actually, it remains quite difficult for me to remember the actual problems I had with it.
But, for example, I need to aggregate the national data on-the-fly to their regions. I need to calculate per Capita data on-the-fly for each variable. Although, one would say this should be simple to accomplish, me and a semi-professional database expert could hardly solve these things.
In one case we came up with as many sub-selects as years were available (or selected by the user) (that can be up to 60 years). Is this "efficient" SQL programming?
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.
Thanks for any recommendations!
Stef
Stefan Schwarzer wrote:
Sorry,
I forgot to mention my table design, which is like this:
name 2001 2002 2003 2004 2005
-----------------------------------------------------------------
Afghanistan ....
Albania ....
(Yep, I know, bad table design.... :-)). I tried to change it to the more common "id | year | value" format, but there were too many SQL problems afterwards for the different queries/calculations we need to have....)
May I suggest that you concentrate on solving *those* problems instead of
the programmatically trivial computation of lowest common value? Notice
that a *really trivial* programming exercise becomes highly involved in
your case --- if I'm understanding correctly what you have, I assume you'd
have to check one by one the fields for NULL or non-NULL values --- that's
intolerably ugly, IMHO, and it is a very simple and direct consequence of
an as-unsound-as-it-gets db/table design.
____________________________________________________________________
Stefan Schwarzer
Lean Back and Relax - Enjoy some Nature Photography:
Appetite for Global Data? UNEP GEP Data Portal:
____________________________________________________________________