On Oct 4, 2007, at 6:41 , Stefan Schwarzer wrote:
So, "value" is something general - it can be (measured in)
(thousand) Dollars, (Million) People, (Hundred) Hectares etc...
Then I would make a separate table for each variable (as I described
above). For one thing, you're going to have keep track of what units
are associated with which variables are somewhere, whether that be in
your database or possibly your middleware. Separating the variables
into separate tables provides better separation of these value types.
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).
If these aggregations are going to be common, you might want to set
up another table that arranges the countries into hierarchies using
nested sets or some other hierarchical strategy (like the adjacency
model or contrib/ltree). I've found nested sets are convenient for
calculating aggregations. You can google or search the archives for
these methods.
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).
You can use nested sets to handle these calculations as well.
I'm sure there are resources out there that describe database schema
for reporting things such as you describe, as it's not a new problem.
I don't know any off the top of my head, but again, Google is your
friend.
Hope this helps!
Michael Glaesemann
grzm seespotcode net
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
