I am trying to create a database structure that will allow me to store records of league matches across multiple seasons and divisions. I have the following rules that need to be maintained:
- A league can contain multiple divisions
- Multiple seasons can take place for a league
- Multiple leagues can take place in the same season
- A team can only play in one division of a league in any given season
- A player can only play for one team in a league in any given season (e.g. Player A cannot play for a team in Division 1 and a team in Division 2 in the same season)
- However a player can play for different teams in different leagues in the same season (e.g. Player A can play for a team in League 1 and a team in League 2 i the same season)
My initial design is in the picture below but I am not sure whether it will meet all of the constraints. I have chosen to link seasons to divisions rather than leagues as the divisions make up the league rather than link them directly. Any advice to improve the design and to help enforce the rules would be greatly appreciated.