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.
Best Answer
Looking at the schema provided, what is the purpose of the
PlayerID
link betweenLeague
andPlayer
? If this is to determine if a player is participating in that league you would need to duplicate eachLeague
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 fromLeague
throughRound
andGame
toPlayer
.I believe this link can be removed and the
PlayerID
column inLeague
dropped. This will resolve any many-to-many relationship issues that I can see.Also you are storing the players name in both
Player
andGame
, is this required for history or can this be normalised and use the data in thePlayer
table throughout the implementation?