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 & increase the query complexity.
Also note that I have made
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