野球データ用のTableスキーマ例

create_baseball_table.sql

# 打撃成績
CREATE TABLE `player_batting` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `number` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
  `team` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `games` int(11) DEFAULT NULL,
  `pa` int(11) DEFAULT NULL,
  `ab` int(11) DEFAULT NULL,
  `r` int(11) DEFAULT NULL,
  `h` int(11) DEFAULT NULL,
  `2b` int(11) DEFAULT NULL,
  `3b` int(11) DEFAULT NULL,
  `hr` int(11) DEFAULT NULL,
  `tb` int(11) DEFAULT NULL,
  `rbi` int(11) DEFAULT NULL,
  `so` int(11) DEFAULT NULL,
  `bb` int(11) DEFAULT NULL,
  `ibb` int(11) DEFAULT NULL,
  `hbp` int(11) DEFAULT NULL,
  `sh` int(11) DEFAULT NULL,
  `sf` int(11) DEFAULT NULL,
  `sb` int(11) DEFAULT NULL,
  `cs` int(11) DEFAULT NULL,
  `dp` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  `ba` float DEFAULT NULL,
  `babip` float DEFAULT NULL,
  `slg` float DEFAULT NULL,
  `ba_risp` float DEFAULT NULL,
  `obp` float DEFAULT NULL,
  `ops` float DEFAULT NULL,
  `rc` float DEFAULT NULL,
  `rc27` float DEFAULT NULL,
  `woba` float DEFAULT NULL,
  `wraa` float DEFAULT NULL,
  `adam_dunn` float DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_year_date_team_player_batting` (`year`,`date`,`team`),
  KEY `ix_player_batting_team` (`team`),
  KEY `idx_year_name_player_batting` (`year`,`name`),
  KEY `idx_year_team_player_batting` (`year`,`team`),
  KEY `ix_player_batting_year` (`year`),
  KEY `idx_year_name_date_player_batting` (`year`,`name`,`date`),
  KEY `idx_year_team_date_player_batting` (`year`,`team`,`date`),
  KEY `ix_player_batting_date` (`date`),
  KEY `idx_year_date_player_batting` (`year`,`date`),
  KEY `ix_player_batting_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=28946 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

# 投球成績
CREATE TABLE `player_pitching` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `number` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
  `team` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `games` int(11) DEFAULT NULL,
  `gs` int(11) DEFAULT NULL,
  `cg` int(11) DEFAULT NULL,
  `sho` int(11) DEFAULT NULL,
  `qs` int(11) DEFAULT NULL,
  `win` int(11) DEFAULT NULL,
  `lose` int(11) DEFAULT NULL,
  `sv` int(11) DEFAULT NULL,
  `hld` int(11) DEFAULT NULL,
  `hp` int(11) DEFAULT NULL,
  `w_per` float DEFAULT NULL,
  `ip` float DEFAULT NULL,
  `bf` int(11) DEFAULT NULL,
  `ab` int(11) DEFAULT NULL,
  `h` int(11) DEFAULT NULL,
  `hr` int(11) DEFAULT NULL,
  `bb` int(11) DEFAULT NULL,
  `hbp` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `k_per` float DEFAULT NULL,
  `wp` int(11) DEFAULT NULL,
  `bk` int(11) DEFAULT NULL,
  `r` int(11) DEFAULT NULL,
  `er` int(11) DEFAULT NULL,
  `era` float DEFAULT NULL,
  `bb9` float DEFAULT NULL,
  `k9` float DEFAULT NULL,
  `k_bb` float DEFAULT NULL,
  `ba` float DEFAULT NULL,
  `whip` float DEFAULT NULL,
  `fip` float DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_player_pitching_date` (`date`),
  KEY `idx_year_date_player_pitching` (`year`,`date`),
  KEY `ix_player_pitching_name` (`name`),
  KEY `idx_year_date_team_player_pitching` (`year`,`date`,`team`),
  KEY `ix_player_pitching_team` (`team`),
  KEY `idx_year_name_player_pitching` (`year`,`name`),
  KEY `idx_year_team_player_pitching` (`year`,`team`),
  KEY `ix_player_pitching_year` (`year`),
  KEY `idx_year_name_date_player_pitching` (`year`,`name`,`date`),
  KEY `idx_year_team_date_player_pitching` (`year`,`team`,`date`)
) ENGINE=InnoDB AUTO_INCREMENT=25970 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

#　年俸
CREATE TABLE `salary` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `team` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL,
  `up_down` int(11) DEFAULT NULL,
  `salary_text` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `up_down_text` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_salary_name` (`name`),
  KEY `ix_salary_salary` (`salary`),
  KEY `ix_salary_up_down` (`up_down`),
  KEY `idx_year_name_salary` (`year`,`name`),
  KEY `ix_salary_team` (`team`),
  KEY `ix_salary_year` (`year`)
) ENGINE=InnoDB AUTO_INCREMENT=684 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;