Ouff.... thanks for all these replies.
A reason for this kind of design.... yeah, I guess these here:
a) not being a professional database designer
b) import through Excel exports... that is, the QC, harmonization and
aggregations are being done in Excel, and then the final result is
being exported into SQL
c) ease of maintenance (in some respect); table overview (we have
some 500 variables and 1500 tables). (I still find it easier to look
for gdp_capita, browse the table and watch out for a specific country/
year/value which I have to change or verify.
We hesitated in deciding which approach to take. But I couldn't
imagine having 1.000.000 or so entries in a single table and sort
them out into the variables/regions/countries for each query or
request...
But as I said, perhaps just the lack of knowing it better.
Stef
On Sep 6, 2007, at 5:27 PM, Alvaro Herrera wrote:
> Franz.Rasper@izb.de wrote:
>>
>>> Make the table:
>>
>>> id | year | value
>>> ---+------+------
>>> 1 | 1970 | 23
>>> 1 | 1971 | 25
>>> 1 | 1972 | 28
>>> ...
>>> 2 | 1972 | 5
>>> 3 | 1971 | 94
>>> 3 | 1972 | 102
>>
>>> primary key: (id,year)
>>> value not null
>>> and be ready.
>>> the import/update reasons are pretty easily solved
>>> that way too.
>>
>> then your primary key will consists of two fields.
>> I prefer the primary keys with one field only.
>
> Is there any reason for that preference?
>
> --
> Alvaro Herrera http://
> www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support