Creating parks table
Parks is another one of those tables that has a ton of columns of which we need only a couple. It’s a standalone table that doesn’t have any columns that could be construed as a foreign key (if we were using them). We’re really only interested in ‘park_id’ and the park factor columns about halfway down:
- ‘avg’
- ‘avg_l’
- ‘avg_r’
- ‘d’
- ‘t’
- ‘hr’
- ‘hr_l’
- ‘hr_r’
And of those, at least for the time being, we’re only interested in ‘avg’. This is the factor we’ll be using to adjust some of the advanced statistics by to account for the specific conditions of the park in which each player played his home games.
CREATE TABLE `parks` (
`park_id` int(11) NOT NULL,
`dimensions_x` smallint(6) DEFAULT NULL,
`dimensions_y` smallint(6) DEFAULT NULL,
`batter_left_x` smallint(6) DEFAULT NULL,
`batter_left_y` smallint(6) DEFAULT NULL,
`batter_right_x` smallint(6) DEFAULT NULL,
`batter_right_y` smallint(6) DEFAULT NULL,
`bases_x0` smallint(6) DEFAULT NULL,
`bases_x1` smallint(6) DEFAULT NULL,
`bases_x2` smallint(6) DEFAULT NULL,
`bases_y0` smallint(6) DEFAULT NULL,
`bases_y1` smallint(6) DEFAULT NULL,
`bases_y2` smallint(6) DEFAULT NULL,
`positions_x0` smallint(6) DEFAULT NULL,
`positions_x1` smallint(6) DEFAULT NULL,
`positions_x2` smallint(6) DEFAULT NULL,
`positions_x3` smallint(6) DEFAULT NULL,
`positions_x4` smallint(6) DEFAULT NULL,
`positions_x5` smallint(6) DEFAULT NULL,
`positions_x6` smallint(6) DEFAULT NULL,
`positions_x7` smallint(6) DEFAULT NULL,
`positions_x8` smallint(6) DEFAULT NULL,
`positions_x9` smallint(6) DEFAULT NULL,
`positions_y0` smallint(6) DEFAULT NULL,
`positions_y1` smallint(6) DEFAULT NULL,
`positions_y2` smallint(6) DEFAULT NULL,
`positions_y3` smallint(6) DEFAULT NULL,
`positions_y4` smallint(6) DEFAULT NULL,
`positions_y5` smallint(6) DEFAULT NULL,
`positions_y6` smallint(6) DEFAULT NULL,
`positions_y7` smallint(6) DEFAULT NULL,
`positions_y8` smallint(6) DEFAULT NULL,
`positions_y9` smallint(6) DEFAULT NULL,
`avg` double DEFAULT NULL,
`avg_l` double DEFAULT NULL,
`avg_r` double DEFAULT NULL,
`d` double DEFAULT NULL,
`t` double DEFAULT NULL,
`hr` double DEFAULT NULL,
`hr_r` double DEFAULT NULL,
`hr_l` double DEFAULT NULL,
`temperature0` smallint(6) DEFAULT NULL,
`temperature1` smallint(6) DEFAULT NULL,
`temperature2` smallint(6) DEFAULT NULL,
`temperature3` smallint(6) DEFAULT NULL,
`temperature4` smallint(6) DEFAULT NULL,
`temperature5` smallint(6) DEFAULT NULL,
`temperature6` smallint(6) DEFAULT NULL,
`temperature7` smallint(6) DEFAULT NULL,
`temperature8` smallint(6) DEFAULT NULL,
`temperature9` smallint(6) DEFAULT NULL,
`temperature10` smallint(6) DEFAULT NULL,
`temperature11` smallint(6) DEFAULT NULL,
`rain0` smallint(6) DEFAULT NULL,
`rain1` smallint(6) DEFAULT NULL,
`rain2` smallint(6) DEFAULT NULL,
`rain3` smallint(6) DEFAULT NULL,
`rain4` smallint(6) DEFAULT NULL,
`rain5` smallint(6) DEFAULT NULL,
`rain6` smallint(6) DEFAULT NULL,
`rain7` smallint(6) DEFAULT NULL,
`rain8` smallint(6) DEFAULT NULL,
`rain9` smallint(6) DEFAULT NULL,
`rain10` smallint(6) DEFAULT NULL,
`rain11` smallint(6) DEFAULT NULL,
`wind` smallint(6) DEFAULT NULL,
`wind_direction` smallint(6) DEFAULT NULL,
`distances0` smallint(6) DEFAULT NULL,
`distances1` smallint(6) DEFAULT NULL,
`distances2` smallint(6) DEFAULT NULL,
`distances3` smallint(6) DEFAULT NULL,
`distances4` smallint(6) DEFAULT NULL,
`distances5` smallint(6) DEFAULT NULL,
`distances6` smallint(6) DEFAULT NULL,
`wall_heights0` smallint(6) DEFAULT NULL,
`wall_heights1` smallint(6) DEFAULT NULL,
`wall_heights2` smallint(6) DEFAULT NULL,
`wall_heights3` smallint(6) DEFAULT NULL,
`wall_heights4` smallint(6) DEFAULT NULL,
`wall_heights5` smallint(6) DEFAULT NULL,
`wall_heights6` smallint(6) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`picture` varchar(200) DEFAULT NULL,
`picture_night` varchar(200) DEFAULT NULL,
`nation_id` int(11) DEFAULT NULL,
`capacity` int(11) DEFAULT NULL,
`type` smallint(6) DEFAULT NULL,
`foul_ground` smallint(6) DEFAULT NULL,
`turf` tinyint(4) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
`relative_path_3d_model` varchar(200) DEFAULT NULL,
`file_name_3d_model` varchar(200) DEFAULT NULL,
`home_team_dugout_is_at_first_base` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`park_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Creating teams table
This table contains, obviously, information about all of the teams in our OOTP universe. Again, there are a bunch of columns that are only useful in-game. We’re interested in the teams’ names, nicknames, league, sub_league, and park_id.
CREATE TABLE `teams` (
`team_id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`abbr` varchar(50) DEFAULT NULL,
`nickname` varchar(50) DEFAULT NULL,
`logo_file_name` varchar(200) DEFAULT NULL,
`city_id` int(11) DEFAULT NULL,
`park_id` int(11) DEFAULT NULL,
`league_id` int(11) DEFAULT NULL,
`sub_league_id` int(11) DEFAULT NULL,
`division_id` int(11) DEFAULT NULL,
`nation_id` int(11) DEFAULT NULL,
`parent_team_id` int(11) DEFAULT NULL,
`level` int(11) DEFAULT NULL,
`prevent_any_moves` tinyint(4) DEFAULT NULL,
`human_team` tinyint(4) DEFAULT NULL,
`human_id` int(11) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
`background_color_id` varchar(8) DEFAULT NULL,
`text_color_id` varchar(8) DEFAULT NULL,
`ballcaps_main_color_id` varchar(8) DEFAULT NULL,
`ballcaps_visor_color_id` varchar(8) DEFAULT NULL,
`jersey_main_color_id` varchar(8) DEFAULT NULL,
`jersey_away_color_id` varchar(8) DEFAULT NULL,
`jersey_secondary_color_id` varchar(8) DEFAULT NULL,
`jersey_pin_stripes_color_id` varchar(8) DEFAULT NULL,
`allstar_team` tinyint(4) DEFAULT NULL,
`historical_id` varchar(50) DEFAULT NULL,
PRIMARY KEY (`team_id`),
INDEX `teams_ix1` (`park_id`),
INDEX `teams_ix2` (`league_id`),
INDEX `teams_ix3` (`sub_league_id`),
INDEX `teams_ix4` (`division_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;