Storing huge amount of user game data

Question

So I have this side project of mine which relies on me agreggation User game data from an API.

What I want to store is the player encouters with other players and saving the result of those encounters aswell. The issue is that the only solution i have managed to come up is to create a Table for each User and have a 3 columns so far, IDPlayer (the player that was played against), GamesWon (the number of games won) and GamesLost (the number of games lost).

This wouldn't be an issue if my expected Player count was above 200k which would mean I would need to create at least 200k tables and each table could theorically contain 200k entries (if those players face each other which is unlikely but still possible). I have read that there could be performance problems once the number of Table have reached 10k.

My question is, is there a better way to handle this sort of information, am I on the wrong path ?

Answer 1

First of all, you need to stop thinking in matrices.

You have 2 options. 1) Store each game & who won. 2) Store aggregates of who won.

Option 1

Store every game and the result of said game. Also allows more data about each game to be recorded within the games row.

create table players
(
  player_id integer,
  player_username varchar(50),
  other_player_related_column datatype,
  ...
  ...
  ...
);

create table games
(
  player1_id  integer, -- references players table
  player2_id  integer, -- references players table
  winner_id integer, -- the player_id of the winner 
  interesting_game_fact datatype -- other game info 
);

Option 2

Just store aggregates of the results of games played between two players. Losses can be derived from the number of games the opponent won. There should only be 1 row for each player_id pair - I suggest storing the lower of the two player_ids as player1_id, just to make life easier.

create table players
(
  player_id integer, 
  player_username varchar(50),
  other_player_related_column datatype,
  ...
  ...
  ...
);

create table results
(
  player1_id  integer, -- references players table
  player2_id  integer, -- references players table
  player1_wins integer, -- number of times player 1 has won
  player2_wins integer -- number of times player 2 has won
);