Storing Huge Amount of User Game Data in MySQL

database-designMySQL

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 ?

Best Answer

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
);

Related Solutions

Design of fact table(s) and dimensions tables for data warehouse

The IBM Redbooks have a couple of good titles on modeling a data warehouse.

You might be better served with an OLTP database in 5NF, though. It's not hard to test simplified versions of both.

Mysql – How to design tables for players in a tournament

UPDATED version based on feedback:

CREATE TABLE group (
    group_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255),
    PRIMARY KEY(team_id)
);

CREATE TABLE player (
    player_id INT NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    group_id INT,
    player_rank INT,
    PRIMARY KEY(player_id)
);

CREATE TABLE game (
    game_id INT NOT NULL AUTO_INCREMENT,
    match_date DATE,
    overtime TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (game_id)
);

CREATE TABLE player_game (
    game_id INT NOT NULL,
    player_id INT NOT NULL,
    player_score INT,
    PRIMARY KEY (game_id, player_id)
);

...and UPDATED explanation: I think group and player are self-explanatory. The table game will have one record per match. The player_game table records the players in a match and the results; each match will have two records, one for each player.

Since you appear to be using InnoDB as the storage engine you may want to use foreign keys to enforce referential integrity, and obviously you can add other constraints and indexes as needed. If you need some functionality that this doesn't seem to cover, ask and I will either explain or add it.