CODEWITHSUNDEEP
sqldatabase-schema
mscavnicky
mscavnicky

Reputation: 145

Database schema for two-player games

Let's have a game played by two players (or teams), where the outcome is represented by a score of both players (e.g. football). Is there an idiomatic way of storing results of such a game in a relational database?

I came up with two possible schemas, none of which seems to be universal enough.

1. One row per game

There will a table games with columns game_id, winner, loser, winner_points, loser_points and each game will be stored in one row of the table.

2. Two rows per game

There will a games table with columns game_id, player, opponent, player_points, opponent_points. Each game will be stored in two rows of the table and they will have the same game_id.

Upvotes: 2

Views: 3773

Answers (2)

bitfiddler
bitfiddler

Reputation: 2115

I would suggest a better relational model with a games table, a player table and a third table to handle many-to-many relationship of players to games. Something like:

game( game_id, date, description, .... )
player( player_id, name, .... )
player_game( player_id, game_id, score )

Now you have a normalized (not redundant, etc.) schema that is very flexible.

To find he winner of a game you can:

select max(score), player_id from player_game where game_id = 'somegame'

To find total points for a player you can:

select sum(score) from player_game where player_id = 'someplayer'

And so on...

Upvotes: 5

AMADANON Inc.
AMADANON Inc.

Reputation: 5919

I would definitely do one row per score (i.e. 2 rows per game). I would store the player, and points, and if I wanted the opponent player/points, I'd look that up separately. I would almost certainly have a separate table for each game, which would mention things like when it was played, etc

No redundant data, everything is easy to gather etc.

Upvotes: 0

Related Questions