flashscore/model/football.sql

CREATE TABLE footballZones (
    zoneID          VARCHAR(255) NOT NULL,
    name            VARCHAR(255) NOT NULL,

    PRIMARY KEY(zoneID),
    UNIQUE(name)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE footballChamps (
    champID         VARCHAR(255) NOT NULL,
    zoneID          VARCHAR(255) NOT NULL,
    name            VARCHAR(255) NOT NULL,

    PRIMARY KEY(champID),
    UNIQUE(zoneID, name),

    FOREIGN KEY (zoneID) REFERENCES footballZones(zoneID)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;


CREATE TABLE footballTeams (
    teamID          VARCHAR(255) NOT NULL,
    zoneID          VARCHAR(255) NOT NULL,
    canonicalName   VARCHAR(255) NOT NULL,
    wasLinked       BOOL         NOT NULL,

    PRIMARY KEY(teamID),
    --UNIQUE(zoneID, canonicalName),

    FOREIGN KEY (zoneID) REFERENCES footballZones(zoneID)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;


CREATE TABLE footballTeamAliases (
    aliasID         INT          NOT NULL AUTO_INCREMENT,
    teamID          VARCHAR(255) NOT NULL,
    name            VARCHAR(255) NOT NULL,

    PRIMARY KEY(aliasID),
    UNIQUE(teamID, name),

    FOREIGN KEY (teamID) REFERENCES footballTeams(teamID)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;


CREATE TABLE footballMatches (
    matchID         VARCHAR(255) NOT NULL,
    startTime       INT          NOT NULL,
    champID         VARCHAR(255) NOT NULL,
    homeTeamID      VARCHAR(255) NOT NULL,
    awayTeamID      VARCHAR(255) NOT NULL,
    homeGoals       INT          NOT NULL,
    awayGoals       INT          NOT NULL,
    hasStats        BOOL         NOT NULL,
    p1HomeGoals     INT          NOT NULL,
    p1AwayGoals     INT          NOT NULL,
    p2HomeGoals     INT          NOT NULL,
    p2AwayGoals     INT          NOT NULL,

    PRIMARY KEY(matchID),

    FOREIGN KEY (champID)    REFERENCES footballChamps(champID),
    FOREIGN KEY (homeTeamID) REFERENCES footballTeams(teamID),
    FOREIGN KEY (awayTeamID) REFERENCES footballTeams(teamID)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

--INSERT INTO footballChamps VALUES (NULL, "Seria A (Italy)");
--INSERT INTO footballChamps VALUES (NULL, "Bundesliga (Germany)");
--INSERT INTO footballChamps VALUES (NULL, "Premier League (England)");

delete from footballTeamAliases;
delete from footballMatches;
delete from footballTeams;
delete from footballChamps;
delete from footballZones;


-- HANDBALL

CREATE TABLE handballZones (
    zoneID          VARCHAR(255) NOT NULL,
    name            VARCHAR(255) NOT NULL,

    PRIMARY KEY(zoneID),
    UNIQUE(name)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE handballChamps (
    champID         VARCHAR(255) NOT NULL,
    zoneID          VARCHAR(255) NOT NULL,
    name            VARCHAR(255) NOT NULL,

    PRIMARY KEY(champID),
    UNIQUE(zoneID, name),

    FOREIGN KEY (zoneID) REFERENCES handballZones(zoneID)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;


CREATE TABLE handballTeams (
    teamID          VARCHAR(255) NOT NULL,
    zoneID          VARCHAR(255) NOT NULL,
    canonicalName   VARCHAR(255) NOT NULL,
    wasLinked       BOOL         NOT NULL,

    PRIMARY KEY(teamID),

    FOREIGN KEY (zoneID) REFERENCES handballZones(zoneID)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;


CREATE TABLE handballTeamAliases (
    aliasID         INT          NOT NULL AUTO_INCREMENT,
    teamID          VARCHAR(255) NOT NULL,
    name            VARCHAR(255) NOT NULL,

    PRIMARY KEY(aliasID),
    UNIQUE(teamID, name),

    FOREIGN KEY (teamID) REFERENCES handballTeams(teamID)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;


CREATE TABLE handballMatches (
    matchID         VARCHAR(255) NOT NULL,
    startTime       INT          NOT NULL,
    champID         VARCHAR(255) NOT NULL,
    homeTeamID      VARCHAR(255) NOT NULL,
    awayTeamID      VARCHAR(255) NOT NULL,
    homeGoals       INT          NOT NULL,
    awayGoals       INT          NOT NULL,
    hasStats        BOOL         NOT NULL,
    p1HomeGoals     INT          NOT NULL,
    p1AwayGoals     INT          NOT NULL,
    p2HomeGoals     INT          NOT NULL,
    p2AwayGoals     INT          NOT NULL,

    PRIMARY KEY(matchID),

    FOREIGN KEY (champID)    REFERENCES handballChamps(champID),
    FOREIGN KEY (homeTeamID) REFERENCES handballTeams(teamID),
    FOREIGN KEY (awayTeamID) REFERENCES handballTeams(teamID)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

--INSERT INTO footballChamps VALUES (NULL, "Seria A (Italy)");
--INSERT INTO footballChamps VALUES (NULL, "Bundesliga (Germany)");
--INSERT INTO footballChamps VALUES (NULL, "Premier League (England)");

delete from handballTeamAliases;
delete from handballMatches;
delete from handballTeams;
delete from handballChamps;
delete from handballZones;