What are some best practices for designing a database structure for storing football player statistics like goals, yellow cards, red cards, and substitutions?

When designing a database structure for storing football player statistics, it is important to create separate tables for players, matches, and statistics. Each statistic (goals, yellow cards, red cards, substitutions) should have its own table with a foreign key linking it to the player and match. This allows for easy retrieval and manipulation of player statistics. 

CREATE TABLE players (
    player_id INT PRIMARY KEY,
    player_name VARCHAR(50)
);

CREATE TABLE matches (
    match_id INT PRIMARY KEY,
    match_date DATE
);

CREATE TABLE goals (
    goal_id INT PRIMARY KEY,
    player_id INT,
    match_id INT,
    FOREIGN KEY (player_id) REFERENCES players(player_id),
    FOREIGN KEY (match_id) REFERENCES matches(match_id)
);

CREATE TABLE yellow_cards (
    yellow_card_id INT PRIMARY KEY,
    player_id INT,
    match_id INT,
    FOREIGN KEY (player_id) REFERENCES players(player_id),
    FOREIGN KEY (match_id) REFERENCES matches(match_id)
);

CREATE TABLE red_cards (
    red_card_id INT PRIMARY KEY,
    player_id INT,
    match_id INT,
    FOREIGN KEY (player_id) REFERENCES players(player_id),
    FOREIGN KEY (match_id) REFERENCES matches(match_id)
);

CREATE TABLE substitutions (
    substitution_id INT PRIMARY KEY,
    player_in_id INT,
    player_out_id INT,
    match_id INT,
    FOREIGN KEY (player_in_id) REFERENCES players(player_id),
    FOREIGN KEY (player_out_id) REFERENCES players(player_id),
    FOREIGN KEY (match_id) REFERENCES matches(match_id)
);

Keywords

database design SQL normalization foreign keys indexing

Related Questions