Game database- many to many relationship

database-design

I am designing a simple relational database about a game that can be played by 2 players each

time. I have these tables: Player, Game, PlayerPlaysGame, Payment. I recognize there is a

many-to-many relationship between Player and Game and one-to-many between Player and Payment

since one player can make many payments whereby one payment can't be made by more than one

player. In PlayerPlaysGame table I have only playerID and gameID attributes. Can I also add

gameDate and gameScore to that table or is it better to have them in Game table?

I'd like to ask if I'm on the right track.

Thank you.

Best Answer

Yes, I believe you are on the right track. The gameDate and gameScore fields are more a part of the game entity and not the player, so they should be on the Gametable. Besides, bridge tables should really only contain the related ID fields.

If gameDate and gameScore were on the PlayerPlaysGame table, they would only be dependent on the gameID and not the playerID. By definition, this would constitute a partial dependency, and would violate second normal form.

Related Solutions

Database design- diagrams

Short answer : yes , add paymentID (PK) to Payments table.
Explanation. Even without knowing any details about your application, I'm pretty sure payment is quite important concept that has to be treated as a separate entity. Thus, it should have primary key. It's possible payment has a composite candidate key (say, combination of (playerID,paymentTimestamp) is very likely to be unique), but it's much easier to deal with simple primary key rather than composite.

As to following requirements.
Class hierarchy doesn't match 100% table structure. There are many concepts that implemented in different ways (for instance, many-to-many relationships, inheritance). Entity identity is one of them , so your approach looks perfectly valid to me.

Mysql – Relational Database tables

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.

Best Answer

Related Solutions

Database design- diagrams

Mysql – Relational Database tables

Related Question