Relational Database tables 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
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 &amp; 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.