Posted by --CELKO-- on 03/06/07 01:48

Here is a guess at what you should have used for DDL if you had fgiven
us specs.

CREATE TABLE PlayerHistory
(player_id INTEGER NOT NULL
REFERENCES Pleyers(player_id)
team_name CHAR(15) NOT NULL
REFERENCES Teams(team_name),
start_year INTEGER NOT NULL
CHECK(start_year BETWEEN 1950 AND 9999),
end_year INTEGER
CHECK(start_year BETWEEN 1950 AND 9999),
CHECK(start_year <= end_year),
PRIMARY KEY ((player_id , team_name ,start_year)
);

A null end_year means the player is still with that team. You use a
VIEW with WHERE end_year IS NULL to get the current situation; you do
not put it in a separate table. What you seem to have is a table in
which an attribute (temproal duration) is split over several rows.

See how simple basic RDBMS design can save you from complex kludges?
Here is a guess at what you should have used for DDL if you had fgiven
us specs.

CREATE TABLE PlayerHistory
(player_id INTEGER NOT NULL
REFERENCES Pleyers(player_id)
team_name CHAR(15) NOT NULL
REFERENCES Teams(team_name),
start_year INTEGER NOT NULL
CHECK(start_year BETWEEN 1950 AND 9999),
end_year INTEGER
CHECK(start_year BETWEEN 1950 AND 9999),
CHECK(start_year <= end_year),
PRIMARY KEY ((player_id , team_name ,start_year)
);

A null end_year means the player is still with that team. You use a
VIEW with WHERE end_year IS NULL to get the current situation; you do
not put it in a separate table. What you seem to have is a table in
which an attribute (temproal duration) is split over several rows.

See how simple basic RDBMS design can save you from complex kludges?

[Back to original message]