Re: Find min year and min value - Mailing list pgsql-general

>> As others have noted, the query *can* be written. But it appears
>> to me
>> that you are struggling against your table layout.
>
> The current schema he has is commonly called EAV (entity-attribute-
> value) and is generally frowned upon. Now, in his particular case
> it may be justified if the "value" column values are actually all
> of the same type, such as currency amounts for each category. If
> this is the case, I suggest renaming the column to be more
> descriptive of what is actually stored: likewise the id_variable
> column.

Hmmm.... I am somewhat surprised to here so. After being told in this
forum how "bad" my old table design was, I changed it to the current
(which is less than alpha). Perhaps to summarize: 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.

The design was a table like this for each variable:


id_country   |    1970    |    1971    |    ...    |    2004    |
2005
-------------------------------------------------------------------
  1                   |   NULL    |      36       |   ...     |
42      |      45
  2 ......


The new like this:

id_variable   |    year    |    value     |    id_country
---------------------------------------
         1             |   2001    |     123       |   1
         1             |   2002    |     125       |   1
         1             |   2003    |     128       |   1
         1             |   2004    |     132       |   1
         1             |   2005    |     135       |   1

         1             |   2001    |     412       |   2
         1             |   2002    |     429       |   2
         1             |   2003    |     456       |   2
         1             |   2004    |     465       |   2
         1             |   2005    |     477       |   2

....

         2             |   1980    |      83       |   1
         2             |   1981    |      89       |   1
....


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. So I thought the most
"common" way would be to have this central table.

But I am at a stage where I still can change - and would very much
like to get your advice.

Thanks a lot!

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
   ____________________________________________________________________