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.
Looking at the schema provided, what is the purpose of the
PlayerID link between
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
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
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