SQL knowing when to split tables

Question

I am creating a database that tracks replays for a game. Each replay has a different game mode that is either team-based gameplay or individual-based gameplay. Depending on the game mode, I want to record the winning team or the winning individual.

I have the following MySQL table that tracks rounds in the replay and the associated winner:

CREATE TABLE replay_rounds (
    replay_id INT UNSIGNED NOT NULL,
    round SMALLINT UNSIGNED NOT NULL,
    winning_player_id INT UNSIGNED,
    winning_team_id TINYINT UNSIGNED,
    FOREIGN KEY (replay_id) REFERENCES replays(id),
    FOREIGN KEY (replay_id, winning_player_id) REFERENCES replay_players(replay_id, player_id),
    FOREIGN KEY (winning_team_id) REFERENCES teams(id),
    PRIMARY KEY (replay_id, round))
CHARACTER SET=utf8
COLLATE=utf8_general_ci
ENGINE=InnoDB;

Using what I have now, if the game mode is team-based then I will set the winning_team_id for each round and set the winning_player_id to null. Likewise, if the game mode is individual-based, I will set the winning_team_id to null.

Performance-wise and in terms of best practice, is it all right to leave it like this? Is there a compelling reason to split this into a separate table for each game mode (even if there's only two modes)? How about a hypothetical situation where game modes are constantly being added -- would this be best resolved by creating a table for every new game mode?

Answer 1

I would just have one winning_id (drop the individual winning ids for team and player) and an additional attribute for game_type_cd. This will be a lookup attribute that is a foreign key to a new table called GAME_TYPE_COES. Then all you need to do is fill in the winning_id and the game type. This will allow for an infinite amount of game types with only having to add data to the GAME_TYPE_CODEs table and not having to change the data structure.

Answer 2

I think this database should be relational - Perhaps 1 table for the (person/team-id), then another that denotes gameid/(team/person-id)/win, then another that has ID/replayinfo/etcetc

So..

TeamID | Name
-------------
  1    | Thunderbirds
  2    | John Petravich

GameID | TeamID | Win?
-----------------------
   1   |    1   |  1
   2   |    2   |  0

ReplayID | GameID | (Your table's other properties)
----------------------------------
    1    |    1   | etc

Now you can use the relationship to determine all of the different information about a particular team or individual. If you need to tailor pages based on what kind of thing they are, add a type column to the first table and display your pages based on that type id.

$.02

Answer 3

I'd suggest to read WP: Database normalization. Especially the Normal Forms are worth knowing about when discussing about splitting/unifying tables...