Data Structure

Data Structure.md

General Structure

• Region
  • Team
    • Player (not implemented)
  • League
    • Season
      • Winner
      • Split
        • Winner
        • Tournament
          • Winner
          • Match
            • Winner
            • Teams Playing
            • Game (not implemented)
              • Winner
              • Teams Playing

SQL Structure

CREATE TABLE IF NOT EXISTS `Regions` (`id` VARCHAR(255) NOT NULL PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `acronym` VARCHAR(255) NOT NULL, `supported` TINYINT(1) NOT NULL DEFAULT 0, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);
CREATE TABLE IF NOT EXISTS `Leagues` (`id` VARCHAR(255) NOT NULL PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `acronym` VARCHAR(255) NOT NULL, `supported` TINYINT(1) NOT NULL DEFAULT 0, `logo` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `RegionId` VARCHAR(255) REFERENCES `Regions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE);
CREATE TABLE IF NOT EXISTS `Seasons` (`id` VARCHAR(255) NOT NULL PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `year` INTEGER NOT NULL, `start` DATE, `end` DATE, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `LeagueId` VARCHAR(255) REFERENCES `Leagues` (`id`) ON DELETE SET NULL ON UPDATE CASCADE);
CREATE TABLE IF NOT EXISTS `Splits` (`id` VARCHAR(255) NOT NULL PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `shortname` VARCHAR(255), `start` DATE, `end` DATE, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `SeasonId` VARCHAR(255) REFERENCES `Seasons` (`id`) ON DELETE SET NULL ON UPDATE CASCADE);
CREATE TABLE IF NOT EXISTS `Tournaments` (`id` VARCHAR(255) NOT NULL PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `shortname` VARCHAR(255) NOT NULL, `teamCount` INTEGER DEFAULT 10, `description` VARCHAR(255), `start` DATE, `end` DATE, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `SplitId` VARCHAR(255) REFERENCES `Splits` (`id`) ON DELETE SET NULL ON UPDATE CASCADE);
CREATE TABLE IF NOT EXISTS `Teams` (`id` VARCHAR(255) NOT NULL PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `acronym` VARCHAR(255) NOT NULL, `logo` VARCHAR(255), `active` TINYINT(1) NOT NULL DEFAULT 0, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `RegionId` VARCHAR(255) REFERENCES `Regions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE);
CREATE TABLE IF NOT EXISTS `Matches` (`id` VARCHAR(255) NOT NULL PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `shortname` VARCHAR(255) NOT NULL, `number_of_games` INTEGER DEFAULT 1, `start` DATETIME, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `TournamentId` VARCHAR(255) REFERENCES `Tournaments` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, `winnerId` VARCHAR(255) REFERENCES `Teams` (`id`) ON DELETE SET NULL ON UPDATE CASCADE);
CREATE TABLE IF NOT EXISTS `PlayTeam` (`createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `MatchId` VARCHAR(255) NOT NULL REFERENCES `Matches` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, `TeamId` VARCHAR(255) NOT NULL REFERENCES `Teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (`MatchId`, `TeamId`));