Alias "all fields"?

SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL
AND .... y2005 NOT NULL

It sounds like a bad table design,
because i think you need an field "f_year" and "value_of_f_year" then
there would be entries like
f_year;value_of_f_year
1970 'NULL'
1970 dfgsd
1971 'NULL'
1971 ....

where f_year IS NOT NULL and value_of_f_year IS NOT NULL

My table design is - due to some import/update reasons - surely not
the best one, but pretty simple:

id y1970 y1971 y1972 ......
1 23 25 28
2 NULL NULL 5
3 NULL 94 102

What do you think?

Stefan Schwarzer schrieb:

SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL
AND .... y2005 NOT NULL

It sounds like a bad table design,
because i think you need an field "f_year" and "value_of_f_year" then
there would be entries like
f_year;value_of_f_year
1970 'NULL'
1970 dfgsd
1971 'NULL'
1971 ....

where f_year IS NOT NULL and value_of_f_year IS NOT NULL

My table design is - due to some import/update reasons - surely not the
best one, but pretty simple:

id y1970 y1971 y1972 ......
1 23 25 28
2 NULL NULL 5
3 NULL 94 102

What do you think?

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.

Regards
Tino

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.

-Franz

Franz.Rasper@izb.de schrieb:

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.

Maybe but this would not help you with this situation here :-)
I think this fittes best but I'm open to alternative approaches.

The wide-table of the original design has definitively much more
problems. And if id is independent from year (not really month or
so) it is usefull imho to have a compound primary key.

Alternatively you can skip the primary key part and just define:

id not null,
year not null,
value not null

unique (id,year)

Regards
Tino

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

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:

Hello All,

I am having a table with 5 columns.
I want to add another column by altering the table at 2nd position
constraint is that I can not drop and recreate the table as column ordering
is of importance.
Is there anyway to do so.

Thanks in advance.

With regards
Ashish...

Ashish Karalkar wrote:

Hello All,

I am having a table with 5 columns.
I want to add another column by altering the table at 2nd position
constraint is that I can not drop and recreate the table as column
ordering is of importance.
Is there anyway to do so.

Copy everything into a tmp table, drop the original, recreate it with
the new column in the correct position, and move the data back into it.

brian