Use effective and end dates. You have players
and teams
entities. Then you would have:
create table player_teams (
player_team_id int generated always as identity primary key,
player_id int not null references players(player_id),
team_id int references teams(team_id),
eff_date date not null,
end_date date
);
This is called a type-2 dimension. The records are "tiled" in time, so the eff_date
of the next record is the end_date
of the previous record.
Note that the team_id
can be NULL
, representing periods when a player is not associated with a team.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…