77 lines
2.3 KiB
MySQL
77 lines
2.3 KiB
MySQL
|
CREATE TABLE teamZones (
|
||
|
|
zoneID VARCHAR(255) NOT NULL,
|
||
|
|
name VARCHAR(255) NOT NULL,
|
||
|
|
sportID INT NOT NULL, -- для вывода
|
||
|
|
|
||
|
|
PRIMARY KEY(zoneID),
|
||
|
|
UNIQUE(name)
|
||
|
|
)
|
||
|
|
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE teamChamps (
|
||
|
|
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 teamZones(zoneID)
|
||
|
|
)
|
||
|
|
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE teams (
|
||
|
|
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 teamZones(zoneID)
|
||
|
|
)
|
||
|
|
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE teamAliases (
|
||
|
|
aliasID INT NOT NULL AUTO_INCREMENT,
|
||
|
|
teamID VARCHAR(255) NOT NULL,
|
||
|
|
name VARCHAR(255) NOT NULL,
|
||
|
|
sportID INT NOT NULL, -- для поиска названий кандидатов без JOIN-ов
|
||
|
|
|
||
|
|
PRIMARY KEY(aliasID),
|
||
|
|
UNIQUE(teamID, name),
|
||
|
|
|
||
|
|
FOREIGN KEY (teamID) REFERENCES teams(teamID)
|
||
|
|
)
|
||
|
|
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
|
||
|
|
|
||
|
|
|
||
|
|
-- это просто cache
|
||
|
|
CREATE TABLE teamMatches (
|
||
|
|
matchID VARCHAR(255) NOT NULL,
|
||
|
|
homeGoals INT NOT NULL,
|
||
|
|
awayGoals INT NOT NULL,
|
||
|
|
hasScoreByPeriods BOOL NOT NULL,
|
||
|
|
p1HomeGoals INT NOT NULL,
|
||
|
|
p1AwayGoals INT NOT NULL,
|
||
|
|
p2HomeGoals INT NOT NULL,
|
||
|
|
p2AwayGoals INT NOT NULL,
|
||
|
|
isMatchScoreLoaded BOOL NOT NULL,
|
||
|
|
isScoreByPeriodsLoaded BOOL NOT NULL,
|
||
|
|
|
||
|
|
PRIMARY KEY(matchID)
|
||
|
|
)
|
||
|
|
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 teamAliases;
|
||
|
|
delete from teamMatches;
|
||
|
|
delete from teams;
|
||
|
|
delete from teamChamps;
|
||
|
|
delete from teamZones;
|