CODEWITHSUNDEEP
database-designdata-structuresrelational-databaseentity-relationship
rnystrom
rnystrom

Reputation: 1916

Data structure/model of score keeping app for games (avoiding many-to-many-to-many)

I'm exploring a simple score-keeping app for playing the card game Rummy just as an exercise. I'm building it for iOS, but that is kind of irrelevant as it uses a SQLite data store so the data structure is applicable in any environment. Let me setup a use-case:

To start keeping score, you first set up your players. I'd like it to be possible to persist players so you can track stats and such. So, you either create new players or select existing.

Then, as you play, you add rounds with scores for each player.

When finished the game, players, and scores are all saved so you can view a log of past games.

The issue I'm running into is that a player can play many games, which have many rounds. But, when viewing a game, it has many rounds, with each round having many players (to display their scores).

Building an ERD for this shows how out-of-hand my perception of this data structure will be.

Silly ERD

It seems like I'm spinning in circles. Are there any examples of structuring this data, or can anyone show me where I'm getting off track?

Upvotes: 2

Views: 3140

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

Facts

  • Game exists.

  • Game has GameRound.

  • Player exists.

  • Player plays GameRound.

Constraints

  • It is possible that some Game has more than one GameRound.

  • It is possible that some Player plays more than one GameRound and that for some GameRound, more than one Player plays that GameRound.

Logical

enter image description here

Upvotes: 5

Declan_K
Declan_K

Reputation: 6826

If you consider the Player table to be the pool of possible players for a Game then you need a GamePlayer table between the Game and Player tables to identify the set of players for that particular game. This table can also hold the position of the four players at the table for that game.

For the PK on this table you could use the combination of GameID and PlayerID, or add an auto-generated ID. IF you go with the latter option (which is what I prefer), you still should have a unique index on the GameID/PlayerID combo. GameID/Position is also a valid candidate for the PK as each game can only have each position filled once.

I would remove the Round table completely and in its place add a Score Table, with RoundNum as a column. That Score would have a foreign key reference back to the GamePlayer table (as only players in that game can actually score points).

Again you have a choice for PK on this table. GamePlayerID/RoundNum has to be unique and is a candidate for the PK, but I would go with the auto-generated ID. And once again you should have a unique index on the GamePlayerID/RoundNum combo.

With Those four tables you can resolve all the M:M relationships.

enter image description here

Upvotes: 3

Related Questions