Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Which RDBMS are you using? –  Phil Apr 25 '13 at 13:12
    
@Phil I'm using MySQL –  user22515 Apr 25 '13 at 13:13

1 Answer 1

up vote 4 down vote accepted

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

create table PLAYER
(
  playerid   INTEGER PRIMARY KEY,
  -- add your other columns here
);

create table GAME
(
  gameid     INTEGER PRIMARY KEY,
  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.

share|improve this answer
    
Thank you very much. Great answer –  user22515 Apr 25 '13 at 13:20
    
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
    
Thank you again. –  user22515 Apr 25 '13 at 13:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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