-1

enter image description here

Would this be the correct layout for a diagram as such? A few of these tables share the same primary key, but I am not sure if this is the best practise/correct relationships that I should set out.

It's for a local level, whereby players don't change teams and assuming that player positions are final. The aim is to gather statistics to show later for analysis.

A. McLean
  • 55
  • 7
  • what happens when a player can play multiple positions? – VikingBlooded May 03 '16 at 17:37
  • @NathanHughes Actually, `Squad` only allows one row per team – Lamak May 03 '16 at 17:41
  • 1
    The big question is what are your needs. soccer statistics may be extremely detailed and may be just keeping match scores. Your diagram doesn't seem to handle players changing teams, or positions, or players playing a different position then the one they are used to, etc'. I've worked in the past on a soccer statistics application, my database was way, way more elaborate then this. – Zohar Peled May 03 '16 at 17:41
  • 1
    I think that the design is messed up. We can't really give much feedback without knowing your requirements. As it is, one player can have only one row in the `PlayerStatistics` table, and that table has the `Team_Id` there, why?; what's the deal with the `Squad` table?, why can you have only one row per team? – Lamak May 03 '16 at 17:44
  • @Paparazzi where are you seeing this? – Lamak May 03 '16 at 17:45
  • It's for a local level, whereby players don't change teams and assuming that player positions are final. The aim is to gather statistics to show later for analysis. – A. McLean May 03 '16 at 17:45
  • @Lamak I was wrong. They can switch teams. But that would be better handled in Players if that is the intent. – paparazzo May 03 '16 at 17:46
  • @A.McLean so, basically, the whole `PlayerStatistics` table can be incorporated in the `Players` table. And also the `Positions` table – Lamak May 03 '16 at 17:47
  • @A.McLean Then add the comment to the question. – paparazzo May 03 '16 at 17:48
  • 1
    And I don't understand the `Squad` table, but, as it is, it can be merged with `Teams` – Lamak May 03 '16 at 17:49
  • Squad is a clear problem and you have failed to include requirements. Voting to close as this question cannot be answered with the information provided. – paparazzo May 03 '16 at 18:02

2 Answers2

1

The Squad table should be a linking table that creates a many to many relationship between Players and Team. Since each Player/Team combination can occur only once, both columns Team_ID and Player_ID should be part of the primary key.

Squad should be on the n-side of two relationships. Its name should probably be something like Membership.

Why do you need a separate PlayerStatistics table? Apparently it stores statistics for the same Player_ID/Team_ID combinations as Squad. The fields of this table should go to the Squad table.

Shouldn't the Positions be per membership? One position per membership, i.e. one player has one defined position in each team, in which case Position_ID should be a column in Squad.

There should be two relationships between Team and MatchStatistics. One on Home_team_ID and one on Away_team_ID.

enter image description here

Alternatively you could associate the PlayerStatistics to Player and Match and thus store what each player has done in each single game. You would then retrieve the overall player statistics or the player-per-team statistics through appropriate queries.

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • I don't see how the `Squad` table creates a many to many relationship, it's primary key is `Team_Id`, so it can be merged with the `Teams` table instead – Lamak May 03 '16 at 17:50
  • 1
    The current design of the OP is wrong. As I say in my answer, the PK should be (Player_ID, Team_ID). Its purpose it to tell which player is in which team and each player can be in several teams and each team can have several players. This is why it **should** create a many-to-many relationship. – Olivier Jacot-Descombes May 03 '16 at 17:53
  • Okay I have changed my layout to suit yours however I have entered a `LeagueTable` as well which has a Team_ID connecting to Team's Team_ID as a foreign key. Inside membership, which value do I use for is-identity? Also I have a button insert method that enters a team name for Team but I want it to create a record (with the same Team_ID) inside my `LeagueTable` (the rest of the values can be null except for its primary key League_ID) – A. McLean May 04 '16 at 14:25
  • I understand a league as a group of sports teams. Therefore there should be a `League_ID` in the `Team` table as foreign key. This forms a one (`League`) to many (`Team`) relationship. See: [What does “Is Identity” column property mean in SQL Server?](http://stackoverflow.com/q/548532/880990). Usually the Primary Key is an identity column, but only if it is not a foreign key. I.e. `Player.Player_ID` can be an identity column, but not `Membership.Player_ID`. – Olivier Jacot-Descombes May 04 '16 at 20:42
0

Messed up in my mind unless you have some strange requirements.

With this design a squad is limited to a single player.

Team_ID is associated with Statistics (not player). If you want a player associated with a single team then do that in Players. And then you should actually merge Statistics with Players.

A link on PK to PK between two tables is rarely a proper design.

If you want a player to be able to play on multiple teams then have PlayerID, TeamID a composite key in Statistics.

You need to disclose the requirements for a proper review. Squad is clearly messed up but you have not stated the purpose of squad.

paparazzo
  • 44,497
  • 23
  • 105
  • 176