CODEWITHSUNDEEP
mysqldatabasenormalization
pr0tocol
pr0tocol

Reputation: 323

Does this database look OK? Did I normalize properly?

I have the need to create a database for some data I have, and so I took a crack at it (as you would hope), and now I need help putting the finishing touches on it! Can you help me normalize this?

I took a picture of the design I created in Excel using fake data:

enter image description here

What you should know is that some games will be played 5v5 and some will be played 3v3. Instead of adding columns for Team1P1, Team1P2, Team1P3, etc., I wanted to leave it up to the experts to help me do this.

Question: Based on my file parser, my application will know whether a game has 6 players (3v3) or 10 (5v5). How can I structure the database to map those players back to the game? Instead of trying to put Player data into the Games table, should I add a column in Players which points to the Game ID ?

Players:
id | player_id | game_id | etc...

Edit for clarification

Picture Starcraft 2, or any other RTS game played online. When a game starts, it is a brand new slate. A set number of players join a match (3v3 or 5v5) and play against each other. At the end of the game, everyone has certain stat values (did they win or lose? How much gold did they have? How many kills, deaths, assists did they have?)

Each of these stats is specific to that player in that game.

If I play 2 games that day, I will look like this:

id | player_id | stat1 | stat2 | stat3 |
1 | 100 | 500 | 600 | 700
2 | 100 | 300 | 999 | 2000

Upvotes: 1

Views: 370

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

Game
----
GameId
Length
Mode
Type
Ranked
PRIMARY KEY (GameId)

Team
----
TeamId
TeamName (only if you need it)
... other stuff about team
PRIMARY KEY (TeamId)

Player
------
PlayerId
PlayerName
... other stuff about player
PRIMARY KEY (PlayerId)

And which player paricipates in which team:

TeamPlayer
----------
TeamId
PlayerId
PRIMARY KEY (TeamId, PlayerId)
FOREIGN KEY (TeamId)
  REFERENCES Team(TeamId)
FOREIGN KEY (PlayerId)
  REFERENCES Player(PlayerId)

and which teams in which game:

GameTeam
--------
GameId
TeamId
PRIMARY KEY (GameId, TeamId)
FOREIGN KEY (GameId)
  REFERENCES Game(GameId)
FOREIGN KEY (TeamId)
  REFERENCES Team(TeamId)

and statistics:

GamePlayerStatistics
--------
GameId
TeamId
PlayerId
... statistics columns per player per game
PRIMARY KEY (GameId, PlayerId)
FOREIGN KEY (GameId, TeamId)
  REFERENCES GameTeam(GameId, TeamId)
FOREIGN KEY (TeamId, PlayerId)
  REFERENCES TeamPlayer(TeamId, PlayerId)

If all your games have 2 teams exactly, the GameTeam table could be removed. You would then need two columns in Game, say Team1Id and Team2Id as Foreign Keys to Team and also some modifications to Statistics table.

Upvotes: 0

WWW
WWW

Reputation: 9870

I mostly agree with PeeHaa's answer, though I have a couple of changes. First, I agree that the id fields in both the tables you propose, pr0tocol, are confusing since you also have game_id and player_id fields. Just use those.

I also agree that you should have a separate game_players table that consists solely of game_id and player_id and both of those fields combined are the primary key of the table.

I feel your stats table should be keyed the same as the game_players table. This way, not only are you keeping track of each individual player's stats, but you also have the ability to report statistics for individual games.

Edit:

Games:

  key
game_id  |  game_length  |  game_mode  |  game_type  |  ranked

Players:

   key
player_id  |  other player data - no stats in here

Game_Players

  key    |     key
game_id  |  player_id

Stats

  key    |     key
game_id  |  player_id  |  stat1  |  stat2  |  stat3  |  etc...

Using these four tables, you can keep track of all stats and be able to separate them based by player or game through whatever reports you choose to write. It doesn't matter now how many games an individual player is assigned to, since it's only part of the key in the relevant tables.

Upvotes: 2

PeeHaa
PeeHaa

Reputation: 72709

I would make another table to map players and games.

I also notice you have stats in the player table.

It might be a better choice the create another table for the player stats. This way you don't need to change the table if you want to add some other stat in the future.

Table game_players:

game id // PK
player id // PK

Tables stats:

id
player id
type (e.g. stat1)
stat

Upvotes: 2

Related Questions