Mysql – Database tables design

You can leave the FK to some dimension tables as NULL if those dimensions are not known or not applicable. You just have to remember to use outer joins when you do your reporting query.

Alternatively, some people create a "none" and/or an "n/a" dimension record for data mart dimensions and then populate fact table FKs to point at these rather than using NULLs. People who do this like this approach because they have an aversion to outer joins.

People who use NULL FKs in fact tables usually have an aversion to people who have an version to outer joins. ;) (in other words, this is a stylistic issue which can generate religious wars)

I say do whichever you prefer, but pick one approach and stick to it fervently.

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.