if tournaments involve many games, then there should be a separate table:
Code:
CREATE TABLE tournament (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NULL,
game_count INT NULL,
team_count InT NULL
PRIMARY KEY(id)
) TYPE=InnoDB;
Then reference it from the game table (nullable in case the game is not part of a tournament).
Code:
CREATE TABLE game (
id INT NOT NULL AUTO_INCREMENT,
home_team_id INT NOT NULL REFERENCES team(id),
guest_team_id INT NOT NULL REFERENCES team(id),
tournament_id INT NULL REFERENCES tournament(id),
home_score INT NULL,
guest_score INT NULL,
game_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) TYPE=InnoDB;
You can also add a many-to-many table to reference teams playing in a tournament:
Code:
CREATE TABLE tournament_team (
tournament_id INT NOT NULL REFERENCES tournament(id),
team_id INT NOT NULL REFERENCES team(id),
PRIMARY KEY (tournament_id, team_id)
) TYPE=InnoDB;
Add one record for each team playing in the tournament.