I have a table Player where playerID is the primary key. Then I have a table Game where gameDate is the primary key (of type DATETIME). Each game is played by 2 players and each player can play many games. So I believe there is a many to many relationship between Player and Game. I want to store both players for each game. I thought of adding player1ID and player2ID on the Game table or to have a bridge table PlayerPlaysGame with playerID and gameDate and then try to find who played the same game so that I get the pair. Any advice as to how I should design my database would be really helpful. Thanks

  • Which RDBMS are you using? – Philᵀᴹ Apr 25 '13 at 13:12
  • @Phil I'm using MySQL – user22515 Apr 25 '13 at 13:13

Given that a game can only ever have two players, I suggest the following design:

create table PLAYER
  -- add your other columns here

create table GAME
  gamedate   DATETIME,
  player1id  INTEGER,
  player2id  INTEGER,
  -- add your other columns here

If you required a differing number of players per game, then I would have designed it differently - with your requirements there is no need to add an extra table & increase the query complexity.

Also note that I have made gameid the PRIMARY KEY. Using a DATETIME as the PK is generally not a good idea - what would happen if two games were created simultaneously?

You'll also probably want to add foreign keys to/from GAME.player1id/player2id and PLAYER.playerid.

|improve this answer|||||
  • What will be the relationship between these two tables? Two players can have the same gameID and one player can play more than 1 games. Is it many-to-many? But if it is , then wouldn't I need a bridge table? – user22515 Apr 25 '13 at 13:29
  • A game only ever has 2 players. You don't ever even need to join the two tables unless you need more information about a specific player. You don't need a bridge table because there are two playerid columns (player1id and player2id) in the GAME table. – Philᵀᴹ Apr 25 '13 at 13:31

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.