Link to home
Create AccountLog in
Avatar of ambience
ambienceFlag for Pakistan

asked on

Best database design for a game database

Im having kind of a developer block trying to design a database for a chess database with support for tournaments. The basic rules are like

1) A player can play chess game as either white/black
2) A game must have two distinct players (no playing with self)
3) Optionally a player can participate in a tournament
4) Tournament has fixed number of participants
5) In a tournament every participant plays against every other participant. A win is 1 point, loss is 0 and draw is 0.5 points


The problems Im having

- Reflecting the first two in the model without relying on CHECK constraints or triggers. Want to find out whether this seemingly simple set of rules can not be reflected with proper normalization only.

6) In a tournament game only tournament participants from the same tournament can compete.

7) Normal or tournament, every game should be associated with the player such that finding all games for a player must be efficient and all games.

--------------

I have tried out several variations, and dont even remember all of them but heres the one that I keep coming to

Player
id

Game
id, game data common to both players (result, status, movelist etc)

GameParticipation
game_id, color (white/black), player_id, other_player_id (player specific game data)

Notes: Every game will have two rows in this table. other_player_id is to speed up lookup by avoiding a join (this is one de-normalization/anomaly that Im willing to control from application)

Tournament
id, ...

TournamentParticipation
tournament_id, player_id, ...

TrounamentGameParticipation
game_id, color, tournament_id, player_id

Notes: Gerund table, Table Per Type: In app model I would want this to derive from GameParticipation.

-----------------

Obvious issues

- A player can play with himself

- In the TournamentGameParticipation table the player_id seems redundant. Anomaly is possible because there is no constraint that the player_id in this table matches the one in GameParticipation.

Any help would be highly appreciated. I am completely open to knowing other issues or even that this is totally crap.
Avatar of felipevidaurri
felipevidaurri
Flag of Mexico image

Avatar of Scott Pletcher
I would drop the TournamentGameParticipation table; just use the standard Game tables:


Game
tournamentId nullable, id, game data common to both players (result, status, movelist etc)
--clus by ( tournamentId, id )

GameParticipation
game_id, color (white/black), player_id
--clus by ( game_id, color ); nonclus on ( player_id, game_id, color )
Avatar of ambience

ASKER

@ScotPletcher: I considered that design but the problem with that is, it does not enforce the participants to be from tournament. Not saying that with TournamentGameParticipation the situation is any better.

@felipevidaurri: Your link is not relevant to the question.
Are you suggesting to drop maintaining tournament participants separately and instead create games whenever someone joins a tournament. This way any game between two players with a tournament id inherently determines the set of games and participants for that tournament?
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I can still have this data

Game
TournamentId = 1, Id = 1

GameParticipation
GameId = 1, Color = white, PlayerId = 1
GameId = 1, Color = black, PlayerId = 2

Tournament
Id = 1

TournamentParticipation
TournamentId = 1, PlayerId = 4

If I drop the separate lists of tournament participations then it may make sense. Also, tournaments are not the only artifacts there can also be club games or ladder games (though for the sake of this question Im OK with keeping them beyond scope).
You can't have that data if you add Tid to GP and enforce a constraint from GP to TP, as I suggested above.
>> Also, tournaments are not the only artifacts there can also be club games or ladder games <<

Just change the Tournament tables to GameType tables, and the same structure works for all 3.
Of course a true design would consist of stepping back and just listing all Attribute values first, only assigning them to Entities later.
You can't have that data if you add Tid to GP and enforce a constraint from GP to TP, as I suggested above.

Missed out the part specific to GP. I get it now.

Of course a true design would consist of stepping back and just listing all Attribute values first, only assigning them to Entities later.

I dont get it. What do you mean by first listing attribute values? If you are referring to the process of designing then in this case I actually started with Entity Framework's Code First and modeled the domain classes and had it generate database. It just does not generate the schema that I would like and customizing it to get the desired effects is a pain.

But even if I took the other route I would have started with Entities and their relationships and mapped them to schema.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you for taking the time and the valuable insights. It really helped - thanks!