时间戳

首页 > 解决方案 > 如何正确组织同一张表中的历史数据？

mysql - 如何正确组织同一张表中的历史数据？

问题描述

主要组织及问题

我正在研究football匹配数据库，该数据库按以下结构组织：

Country -> Competition -> Season -> Data

问题的基本问题是我不太确定我的数据库模式是否正确。

解释数据结构

主要的“问题”是每个Data都按特定季节分组，让我们考虑一下国家England：

Country | Competition    | Season    |
England | Premier League | 2017/2018 |
England | Premier League | 2016/2017 |
England | Premier League | 2015/2016 |
England | Premier League | 2014/2015 |
England | Premier League | 2013/2014 |

如您所见，England有一个名为比赛的比赛Premier League，分为 5 个赛季。

每一个都competition被分成rounds，一个比赛可以由单个组成round，也可以由多个组成rounds。

每个round都可以分成groups，这取决于比赛的类型，有些比赛是不分组的。

数据库结构

根据我对数据关系的解释，我配置了一个具有下表的数据库结构：

  1. 国家：包含所有可用的国家信息。
  2. 比赛：包含所有比赛的详细信息。
  3. Competition_seasons：包含所有的比赛赛季。
  4. Competition_rounds：包含可用于比赛的所有回合。
  5. Competition_groups：包含所有可用于比赛的组。
  6. League_ranking：包含参加特定比赛的每支球队的所有排名位置。

数据库架构是这样的（我没有足够的代表来显示您需要单击链接的图像）：

在此处输入图像描述

数据库代码

-- -----------------------------------------------------
-- Table `mydb`.`country`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`country` (
  `id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `link` VARCHAR(255) NOT NULL,
  `iso` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`competition`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`competition` (
  `id` INT NOT NULL,
  `country_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `link` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `id_idx` (`country_id` ASC),
  CONSTRAINT `FK_country_competition_country_id`
    FOREIGN KEY (`country_id`)
    REFERENCES `mydb`.`country` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`competition_seasons`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`competition_seasons` (
  `season_id` INT NOT NULL,
  `competition_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `create_at` DATETIME NULL,
  `update_at` DATETIME NULL,
  INDEX `competition_id_idx` (`competition_id` ASC),
  PRIMARY KEY (`season_id`),
  CONSTRAINT `FK_competition_competition_seasons_competition_id`
    FOREIGN KEY (`competition_id`)
    REFERENCES `mydb`.`competition` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`competition_groups`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`competition_groups` (
  `group_id` INT NOT NULL,
  `competition_id` INT NOT NULL,
  `round_id` INT NOT NULL,
  INDEX `group_id_idx` (`group_id` ASC),
  INDEX `competition_id_idx` (`competition_id` ASC),
  INDEX `round_id_idx` (`round_id` ASC),
  CONSTRAINT `FK_group_competition_groups_group_id`
    FOREIGN KEY (`group_id`)
    REFERENCES `mydb`.`group` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_competition_competition_groups_competition_id`
    FOREIGN KEY (`competition_id`)
    REFERENCES `mydb`.`competition` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_round_competition_groups_round_id`
    FOREIGN KEY (`round_id`)
    REFERENCES `mydb`.`round` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`competition_rounds`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`competition_rounds` (
  `competition_id` INT NOT NULL,
  `round_id` INT NOT NULL,
  INDEX `competition_id_idx` (`competition_id` ASC),
  INDEX `round_id_idx` (`round_id` ASC),
  CONSTRAINT `FK_competition_competition_rounds_competition_id`
    FOREIGN KEY (`competition_id`)
    REFERENCES `mydb`.`competition` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_round_competition_rounds_round_id`
    FOREIGN KEY (`round_id`)
    REFERENCES `mydb`.`round` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`league_ranking`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`league_ranking` (
  `id` INT NOT NULL,
  `position` INT NULL,
  `team_id` INT NULL,
  `season_id` INT NULL,
  `round_id` INT NULL,
  `competition_id` INT NULL,
  `group_id` INT NULL,
  `played_matches` INT NULL,
  `wins` INT NULL,
  `draws` INT NULL,
  `losses` INT NULL,
  `goals_for` INT NULL,
  `goals_against` INT NULL,
  `goals_difference` INT NULL,
  `points` INT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_team_league_ranking_teamd_id`
    FOREIGN KEY (`id`)
    REFERENCES `mydb`.`team` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_round_league_ranking_round_id`
    FOREIGN KEY (`id`)
    REFERENCES `mydb`.`round` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_competition_league_ranking_competition_id`
    FOREIGN KEY (`id`)
    REFERENCES `mydb`.`competition` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_group_league_ranking_group_id`
    FOREIGN KEY (`id`)
    REFERENCES `mydb`.`group` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

我的数据库架构是否适合商店历史季节？

标签： mysqlsqldatabase-designdatabase-schema

解决方案

#1。首先，我不明白“competition_round”表的用法，这里你用两列“competition_id”和“round_id”定义了这个表。无论如何，您已经在“leage_ranking”表中定义了“competition_id”和“round_id”，所以，我建议不要再使用额外的表来存储相同的数据。

#2。正如您所提到的，您希望历史地存储数据，那么我假设交易量可能会减少。所以，我建议你去规范化表格。因为最终您将分析数据并对历史数据执行大量数据挖掘以提供适当的业务预期。

#3。除此之外，根据我的经验，它似乎是一个很好的数据模型。

请参阅此链接以了解数据库设计模式

推荐阅读