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
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.
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).
- Ranking scoreboard (I'm probably gonna use the Elo rating system)
- 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.