Question :
I am designing a Tournament Schema for Scrabble tournaments, including Leagues and Rounds for each Tournament. The following is the initial schema design we have.
A Tournament has many leagues, a league has many rounds, and a round has many games. What we can’t work out is where we store the players without generating many-to-many relationships.
We’re looking for ways in which we can improve the design from here as I’m not sure this is going to work as is.
Answer :
Looking at the schema provided, what is the purpose of the PlayerID link between League and Player? If this is to determine if a player is participating in that league you would need to duplicate each League record for every player in that league which would break your primary key. In order to determine if a player is part of a League you would need to join from League through Round and Game to Player.
I believe this link can be removed and the PlayerID column in League dropped. This will resolve any many-to-many relationship issues that I can see.
Also you are storing the players name in both Player and Game, is this required for history or can this be normalised and use the data in the Player table throughout the implementation?
If a player can move from one league to another after each season, I wouldn’t put the scores in the Player table or you would lose all history. Instead you can have a Ranking table with relationships to League and Player.