Relational Database tables

Question

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

• Philᵀᴹ: Which RDBMS are you using?
• user22515: @Phil I'm using MySQL

Answer 1

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.

• user22515: 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?
• Philᵀᴹ: 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.