Database Structure for 2v2 game

Question

I regularly play a 2v2 game with 12 friends and I want a database to keep track of players, teams, scores and games, with the intent of creating a ranking system.

Since we regularly change teams I've come up with tables players, teams and games where the games have two teams (team1 and team2) and the teams consist of two players (player1 and player2).

This causes quite a few problems - for example if I pick two players (let's call them A and B) to play together, I have to check if there already exists a team where Player1 is A and Player2 is B or Player1 is B and Player2 is A.

The columns games and wins are present in both the players table and teams table - but this is because I want to see both how many games are won by the players, but also how compatible the player is in different teams (how often a player wins when teamed up with another specific player).

1. Ranking scoreboard (I'm probably gonna use the Elo rating system)
2. A statistics page for every player with rating, wins, games, recent games statistics, and which players he is most compatible with.

I strongly suspect that much of this violates some of the principles in database normalization, and I would love some suggestions as how to implement my database design.

Answer 1

There are two issues that I see with your current schema, one is the issue of having to check two fields in a table to determine if a compound key is effectively a duplicate, and, some aggregate data is rolled up into the individual tables for separate entities (wins, especially, but also potentially a player's rating).

For the first issue, there aren't in-DB tricks to make either/any field of a compound key be treated in the OR manner that you're looking for, but if your DB supports it, you could create a function getPlayerTeams(player_id) to encapsulate the query.

(You could also create a view with the team_thumbprint calculated as a hash of the sorted player ids, so that any combo of the same two people always results in the same thumbprint, but that might be a bit much here).

As far as normalization, consider separating out entities from results that occur by using a team_result table to track all results for a given team. A little more extreme normalization would also call for a player_rating_hist table, containing all rating changes for a player. Their current rating is simply the one with the most recent date. A player view could also be used to contain the most recent value for easy querying.

Proposed schema (sorry no diagram):

player
    id
    name
    created_on
    updated_on

player_rating_hist
    player_id (FK)
    rating
    rating_date

team
    id
    player1_id (FK)
    player2_id (FK)
    created_on
    updated_on

game
    id
    team1_id (FK)
    team2_id (FK)

team_game
    team_id (FK)
    game_id (FK)
    result
    score
    rating_change

team_rating_hist
    team_id (FK)
    rating
    rating_date

Queries:

--Results for the game, should only ever be two rows for any given game
SELECT * FROM team_game WHERE game_id = 101

--All results for a team
SELECT * FROM team_game WHERE team_id = 123456 

This structure allows separating out "base" entities (players and teams) from the "content" that occurs as a result of the system running over time, and means you're not constantly updating one of the base tables with the current rating, # of wins, etc. Those are derived values and should be retrieved by getting the most recent rating, average rating, COUNT of wins or losses, etc. If the system got big enough, you could consider extracting such aggregate data into a separate "warehouse" (even if it was just a separate set of tables in the same DB) for easier analytics.