I am developing a database for a Fantasy Cricket league for my Database module in college.
I had done the design and was fairly confident that I had a working version, but I suddenly realised that I hadn't handled the swapping of players.
This is a snippet of my diagram since the final version i have is still on paper.
I had this sudden realisation that the total points for an owners squad would be calculated based on the points of the players in the current squad, whether those players had been there all along or not.
How do I maintain the historic data of all the players in a specific squad and their points while they were in that squad?
I'm not sure I'm very clear here.. But would really appreciate the help.
One option is to insert a join table between player and squad, that maps a player to a squad for inerval of time. This way you can select all the players from a given squad for given time interval. Having the players, it should be easy to access the match information, since it is directly connected with a player.