sql - Best way to store relationship history in a postgreSQL database

Question

I am trying to model a postgreSQL database. I have a lot of relations that I need to keep track of. For example I have a table called Team and other called Player. All players are naturally related to a team so there is a one-to-many relationship between them. It is easy if I need to keep record only of the current team. I could easily update the team field in the player record if the team changes.

In my case I need to know also the previous teams and when the player played for each team.

What is the correct way to manage this relationship?

I was thinking that I could have a third table between those two: PlayerTeam that would contain these fields: id, startDate, endDate, teamId and playerId.

The information that I need most often is the current team so joining that additional table would add some overhead.

Would it be a good idea to add a separate currentTeam in Player table? That would be maybe a bit redundant as I already could get that info from the PlayerTeam table looking for a record where endDate is null

I feel like am in way over my head here. I hope that somebody can point me in the right direction.

question from:https://stackoverflow.com/questions/65872336/best-way-to-store-relationship-history-in-a-postgresql-database

Answer 1

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.