MySQL – Avoid Redundancy in Tournament Software Database

MySQL

I'm about to write a tournament software. Therefor I have two db-tables in mysql database:
players and matches.
In matches I save every single match played in the tournament:

ID_player1, ID_player2, Goals_player1, Goals_player2

I would like to sum up all goals from all matches of every player, so I can display a ranking, where won mathes and goals and countergoals are displayed. Now the question is, how to realize this in the database: I see several options:

Save goals and countergoals in the players table. Every time a match is entered into the database, the goals and countergoals are added to the related player. (but what about redundancy..?)
Save goals and countergoals in the players table. But every time a match is entered into the database, run a query over the matches database and sum up all the goals every time again and again. This way I would at least avoid possible inconsistency.
Don't save goals and countergoals in players table. But every time I would like to display the players rank with all goals and so, there would be a humangous database query running over the whole matches table for every single player. This is probably not very smart when it comes to performance, is it?

So what is the best way to store that information..?
I appreciate every help or hint.
greets and thanks in advance!

Best Answer

Start by having a table with one row for each goal-like thingie. It would include player_id, game_id, etc. From that you can do SELECT COUNT(*) ... GROUP BY player_id, goal_type to get counts.

You can either do that 'live' when you need the goal counts, or you can use that SELECT to populate some kind of summary table.

After you have tried all of that, you will have all the data stored so that if the forumlas are wrong, you can repair the counts.

Then you can think about doing the alternatives. Think of this as being a learning exercise.

Related Solutions

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.

Mysql – Relational Database tables

Given that a game can only ever have two players, I suggest the following design:

create table PLAYER
(
  playerid   INTEGER PRIMARY KEY,
  -- add your other columns here
);

create table GAME
(
  gameid     INTEGER PRIMARY KEY,
  gamedate   DATETIME,
  player1id  INTEGER,
  player2id  INTEGER,
  -- add your other columns here
);

If you required a differing number of players per game, then I would have designed it differently - with your requirements there is no need to add an extra table & increase the query complexity.

Also note that I have made gameid the PRIMARY KEY. Using a DATETIME as the PK is generally not a good idea - what would happen if two games were created simultaneously?

You'll also probably want to add foreign keys to/from GAME.player1id/player2id and PLAYER.playerid.

Best Answer

Related Solutions

Mysql – How to design tables for players in a tournament

Mysql – Relational Database tables

Related Question