Having 500 statistical global national variables for about 240
countries/territories. Need to do regional aggregations, per
Capita calculations and some completeness computations on-the-fly.
id_variable | year | value | id_country
Both Steve and I have given you alternatives and reasons for
choosing alternative schema. You haven't provided any additional
information to really help us guide you in any particular direction
from what we already have. For example, in the section from me
which you quoted above, I wrote that this schema may be appropriate
if the "value" column values are actually all of the same type
(e..g, all currency amounts, all masses, all counts). You haven't
said whether or not this is the case. We can't read your mind :)
Again, one thing that would help is if you use a more descriptive
column name than "value" that gives an indication of what *kind* of
values are in the column.
I thought (and did ask) about the possibility to put nevertheless
- with the new table design - the variables into different tables,
but nobody really got my on a track for that.
Steve first suggested it and I provided an example of what that
would look like (using "gdp" and "fish_catches" tables) in the same
post you quoted from above.
http://archives.postgresql.org/pgsql-general/2007-10/msg00108.php
Is this not what you mean?
Hmm... sorry that I didn't give you more info. I will see it I can do
better now.
So, as mentioned above, we have 500 different variables (such as GDP,
Fish Catch, Population, Forest Cover, Fertility Rate etc.). Many of
them have indeed different units (Metric Tons, Thousand People, Sqkm,
Persons per Year etc.). And many do cover different time periods
(1970-2005; 2000-2002; 1970-75, 1975-80,..., 2000-05; ...).
So, "value" is something general - it can be (measured in) (thousand)
Dollars, (Million) People, (Hundred) Hectares etc...
We update the variables something like once a year or every two
years, in an ongoing manner. Updating means especially adding new
years (as they are being published by World Bank, UNSD, etc.). So, no
real "hard core" editing/adding, I'd say. A slow, but steady progress.
Queries, as mentioned too, are mainly simple "SELECt * FROM
specific_variable_like_gdp". But as well on-the-fly aggregations to
subregions and regions (such as West Africa, and Africa). And some
calculations which enables to indicate the "completeness" of the
aggregations (parameters being used are "Number of countries included
in aggregation in respect to Number of Countries, Total Population,
Land Area, GDP).
Does this help?
Thanks anyway already for all your feedback. Your really great people
out there!!
Best wishes,
Stef
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
