2

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.

enter image description here

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.

1

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?

  • Thanks for this, we came to the same conclusion after looking into the schema a little more. It makes a lot more sense! – Powellellogram Feb 21 '17 at 12:33
  • Excellent, good to know you are actively trying to solve the problem and improve your knowledge. – Mayberg Feb 21 '17 at 12:35
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.