Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
72 views
in Technique[技术] by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...