Author: cavebutter

Tables 7: Player Career Fielding Stats

cavebutterLeave a comment

Fielding stats are the last group of stats I am going to look at, so I don’t have all that much to say about this table at the moment.  I set up indexes on the same fields as I did for the Career Batting Stats table.

There are a few columns that I need to understand still:

  • ‘rto’
  • ‘ipf’
  • We also have a calculated stat for Zone Rating.  This one, too, would be pretty hard to calculate on our own (though I would like to try!)  There’s definitely not enough of the right data in this table to calculate it or UZR or other advanced defensive metrics without additional gameplay data.  It’s likely that it’s in the data dump options somewhere, but I haven’t looked hard enough yet to say.
CREATE TABLE `players_career_fielding_stats` (
  `pcfs_id` int(11) NOT NULL Auto_Increment,    
  `player_id` int(11) NOT NULL,
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `league_id` int(11) DEFAULT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) DEFAULT NULL,
  `position` smallint(6) NOT NULL,
  `tc` smallint(6) DEFAULT NULL,
  `a` smallint(6) DEFAULT NULL,
  `po` smallint(6) DEFAULT NULL,
  `er` smallint(6) DEFAULT NULL,
  `ip` smallint(6) DEFAULT NULL,
  `g` smallint(6) DEFAULT NULL,
  `gs` smallint(6) DEFAULT NULL,
  `e` smallint(6) DEFAULT NULL,
  `dp` smallint(6) DEFAULT NULL,
  `tp` smallint(6) DEFAULT NULL,
  `pb` smallint(6) DEFAULT NULL,
  `sba` smallint(6) DEFAULT NULL,
  `rto` smallint(6) DEFAULT NULL,
  `ipf` smallint(6) DEFAULT NULL,
  `plays` smallint(6) DEFAULT NULL,
  `plays_base` smallint(6) DEFAULT NULL,
  `roe` smallint(6) DEFAULT NULL,
  `zr` double DEFAULT NULL,
  PRIMARY KEY (`pcfs_id`),
  INDEX `pcfs_ix1` (`league_id`),
  INDEX `pcfs_ix2` (year),
  INDEX `pcfs_ix3` (`player_id`),
  INDEX `pcfs_ix4` (`team_id`),
  INDEX `pcfs_ix5` (`split_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tables 6: Player Career Batting Stats

cavebutter1 Comment

This table contains all batting statistics for all players for all years.  If data is dumped in the middle of a year, that year’s stats are also in this table.  There are multiple records for every year based on a number of columns.  I added an AUTO_INCREMENT id column to avoid a composite PK made up of the following:

  • ‘player_id’
  • ‘year’
  • ‘split_id’ – as mentioned earlier, this breaks out a players stats overall, vs lefties, and vs righties
  • ‘stint’ – a player gets a new record if he changes teams during the course of a season

We’re indexing all of these as well as ‘team_id’.

There are two columns that contain static records of calculated stats:  ‘wpa’ and ‘war’.

Win Percentage Added  is a stat that I don’t find much use for.  Essentially it sums the percentage increase of a team’s chances of winning after an at-bat.  I believe it is a real measure, but I feel that there are other measures that better capture offensive contribution without relying on situational hitting and specific game scenarios.

Wins Above Replacement is a counting stat that takes offense, base running, and defense into account and represents it as a number of wins that a player is responsible for above a replacement level player.  It’s a complicated formula that relies on some defensive metrics that I don’t fully understand yet, so it’s nice that it’s here.  Particularly because this is a counting stat, it can stand alone.

CREATE TABLE `players_career_batting_stats` (
  `pcbs_id` int(11) NOT NULL AUTO_INCREMENT,
  `player_id` int(11) NOT NULL,
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `game_id` int(11) DEFAULT NULL,
  `league_id` int(11) DEFAULT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) NOT NULL,
  `position` smallint(6) DEFAULT NULL,
  `ab` smallint(6) DEFAULT NULL,
  `h` smallint(6) DEFAULT NULL,
  `k` smallint(6) DEFAULT NULL,
  `pa` smallint(6) DEFAULT NULL,
  `pitches_seen` smallint(6) DEFAULT NULL,
  `g` smallint(6) DEFAULT NULL,
  `gs` smallint(6) DEFAULT NULL,
  `d` smallint(6) DEFAULT NULL,
  `t` smallint(6) DEFAULT NULL,
  `hr` smallint(6) DEFAULT NULL,
  `r` smallint(6) DEFAULT NULL,
  `rbi` smallint(6) DEFAULT NULL,
  `sb` smallint(6) DEFAULT NULL,
  `cs` smallint(6) DEFAULT NULL,
  `bb` smallint(6) DEFAULT NULL,
  `ibb` smallint(6) DEFAULT NULL,
  `gdp` smallint(6) DEFAULT NULL,
  `sh` smallint(6) DEFAULT NULL,
  `sf` smallint(6) DEFAULT NULL,
  `hp` smallint(6) DEFAULT NULL,
  `ci` smallint(6) DEFAULT NULL,
  `wpa` double DEFAULT NULL,
  `stint` smallint(6) NOT NULL,
  `war` double DEFAULT NULL,
  PRIMARY KEY (`pcbs_id`),
  INDEX `pcbs_ix1` (`league_id`),
  INDEX `pcbs_ix2` (year),
  INDEX `pcbs_ix3` (`player_id`),
  INDEX `pcbs_ix4` (`team_id`),
  INDEX `pcbs_ix5` (`split_id`)    
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tables 5: The Players Table

cavebutterLeave a comment

Creating players table

This table contains all of the biographic and demographic detail of all players.  A couple notes here.

  • ‘team_id’ and ‘league_id’ refer to the team and league of a given player at the time of the last data dump.  We will be ignoring these columns from this table in all of our career and prior-year stats.  They will be useful when we’re looking for a player’s team mid-season.
  • At some point, I posted a request for a new feature.  The request was that although we can view a coach’s former player profile, we can’t see a player’s coach profile if that player had retired and became a coach.  That feature can be implemented by adding a column ‘coach_id’ to this table.
CREATE TABLE `players` (
  `player_id` int(11) NOT NULL,
  `team_id` int(11) DEFAULT NULL,
  `league_id` int(11) DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `nick_name` varchar(50) DEFAULT NULL,
  `age` smallint(6) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `city_of_birth_id` int(11) DEFAULT NULL,
  `nation_id` int(11) DEFAULT NULL,
  `second_nation_id` int(11) DEFAULT NULL,
  `weight` smallint(6) DEFAULT NULL,
  `height` smallint(6) DEFAULT NULL,
  `retired` tinyint(4) DEFAULT NULL,
  `free_agent` tinyint(4) DEFAULT NULL,
  `last_league_id` int(11) DEFAULT NULL,
  `last_team_id` int(11) DEFAULT NULL,
  `organization_id` int(11) DEFAULT NULL,
  `last_organization_id` int(11) DEFAULT NULL,
  `language_ids0` int(11) DEFAULT NULL,
  `language_ids1` int(11) DEFAULT NULL,
  `uniform_number` smallint(6) DEFAULT NULL,
  `experience` smallint(6) DEFAULT NULL,
  `person_type` smallint(6) DEFAULT NULL,
  `bats` smallint(6) DEFAULT NULL,
  `throws` smallint(6) DEFAULT NULL,
  `personality_greed` smallint(6) DEFAULT NULL,
  `personality_loyalty` smallint(6) DEFAULT NULL,
  `personality_play_for_winner` smallint(6) DEFAULT NULL,
  `personality_work_ethic` smallint(6) DEFAULT NULL,
  `personality_intelligence` smallint(6) DEFAULT NULL,
  `personality_leader` smallint(6) DEFAULT NULL,
  `historical_id` varchar(50) DEFAULT NULL,
  `historical_team_id` varchar(50) DEFAULT NULL,
  `best_contract_offer_id` int(11) DEFAULT NULL,
  `injury_is_injured` tinyint(4) DEFAULT NULL,
  `injury_dtd_injury` tinyint(4) DEFAULT NULL,
  `injury_career_ending` tinyint(4) DEFAULT NULL,
  `injury_dl_left` smallint(6) DEFAULT NULL,
  `injury_dl_playoff_round` smallint(6) DEFAULT NULL,
  `injury_left` smallint(6) DEFAULT NULL,
  `dtd_injury_effect` smallint(6) DEFAULT NULL,
  `injury_id` int(11) DEFAULT NULL,
  `prone_overall` smallint(6) DEFAULT NULL,
  `prone_leg` smallint(6) DEFAULT NULL,
  `prone_back` smallint(6) DEFAULT NULL,
  `prone_arm` smallint(6) DEFAULT NULL,
  `fatigue_pitches0` smallint(6) DEFAULT NULL,
  `fatigue_pitches1` smallint(6) DEFAULT NULL,
  `fatigue_pitches2` smallint(6) DEFAULT NULL,
  `fatigue_pitches3` smallint(6) DEFAULT NULL,
  `fatigue_pitches4` smallint(6) DEFAULT NULL,
  `fatigue_pitches5` smallint(6) DEFAULT NULL,
  `fatigue_points` smallint(6) DEFAULT NULL,
  `fatigue_played_today` tinyint(4) DEFAULT NULL,
  `running_ratings_speed` smallint(6) DEFAULT NULL,
  `running_ratings_stealing` smallint(6) DEFAULT NULL,
  `running_ratings_baserunning` smallint(6) DEFAULT NULL,
  `position` smallint(6) DEFAULT NULL,
  `role` smallint(6) DEFAULT NULL,
  `college` tinyint(4) DEFAULT NULL,
  `draft_year` smallint(6) DEFAULT NULL,
  `draft_round` smallint(6) DEFAULT NULL,
  `draft_supplemental` tinyint(4) DEFAULT NULL,
  `draft_pick` smallint(6) DEFAULT NULL,
  `draft_overall_pick` smallint(6) DEFAULT NULL,
  `draft_eligible` tinyint(4) DEFAULT NULL,
  `hidden` tinyint(4) DEFAULT NULL,
  `draft_league_id` int(11) DEFAULT NULL,
  `draft_team_id` int(11) DEFAULT NULL,
  `turned_coach` tinyint(4) DEFAULT NULL,
  `hall_of_fame` tinyint(4) DEFAULT NULL,
  `rust` smallint(6) DEFAULT NULL,
  `inducted` smallint(6) DEFAULT NULL,
  `strategy_override_team` tinyint(4) DEFAULT NULL,
  `strategy_stealing` int(11) DEFAULT NULL,
  `strategy_running` int(11) DEFAULT NULL,
  `strategy_bunt_for_hit` int(11) DEFAULT NULL,
  `strategy_sac_bunt` int(11) DEFAULT NULL,
  `strategy_hit_run` int(11) DEFAULT NULL,
  `strategy_hook_start` int(11) DEFAULT NULL,
  `strategy_hook_relief` int(11) DEFAULT NULL,
  `strategy_pitch_count` int(11) DEFAULT NULL,
  `strategy_only_allow_single_inning_saves` tinyint(4) DEFAULT NULL,
  `strategy_pitch_around` int(11) DEFAULT NULL,
  `strategy_sticky_lineup` tinyint(4) DEFAULT NULL,
  `strategy_sticky_position` tinyint(4) DEFAULT NULL,
  `strategy_no_pinch_if_rested` tinyint(4) DEFAULT NULL,
  `strategy_never_pinch_hit` tinyint(4) DEFAULT NULL,
  `strategy_defensive_sub` tinyint(4) DEFAULT NULL,
  `strategy_never_defensive_sub_for` tinyint(4) DEFAULT NULL,
  `local_pop` smallint(6) DEFAULT NULL,
  `national_pop` smallint(6) DEFAULT NULL,
  `draft_protected` tinyint(4) DEFAULT NULL,
  `morale` smallint(6) DEFAULT NULL,
  `morale_player_performance` smallint(6) DEFAULT NULL,
  `morale_team_performance` smallint(6) DEFAULT NULL,
  `morale_team_transactions` smallint(6) DEFAULT NULL,
  `expectation` smallint(6) DEFAULT NULL,
  `morale_player_role` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`player_id`),
  INDEX `players_ix1` (`team_id`),
  INDEX `players_ix2` (`league_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tables 4: Parks and Teams

cavebutterLeave a comment

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;

Tables 3: The League History Tables.

cavebutterLeave a comment

Edited based on some really useful tips from the OOTP Community’s resident MySQL whisperer

Where in Lahman, you have to build your own views for league statistics by season, OOTP does that for you.  This is nice.  I have a couple issues with the some of the columns in these tables and I will explain them as we build them.  Also, here’s where I started adding AUTO_INCREMENT ID fields to these tables.  I tried playing around with massive, composite PK’s and it wasn’t good.

Creating the league_history table

So, I thought this table was going to be useful when I configured the MySQL dump in-game.  Turns out, it’s not all that useful for my purposes.  It just identifies the award winners for each year in each league and sub_league.  More out of laziness than anything else, I am keeping it in the schema:

CREATE TABLE `league_history` (
  `league_id` int(11) NOT NULL,
  `sub_league_id` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `best_hitter_id` int(11) DEFAULT NULL,
  `best_pitcher_id` int(11) DEFAULT NULL,
  `best_rookie_id` int(11) DEFAULT NULL,
  `best_manager_id` int(11) DEFAULT NULL,
  `best_fielder_id0` int(11) DEFAULT NULL,
  `best_fielder_id1` int(11) DEFAULT NULL,
  `best_fielder_id2` int(11) DEFAULT NULL,
  `best_fielder_id3` int(11) DEFAULT NULL,
  `best_fielder_id4` int(11) DEFAULT NULL,
  `best_fielder_id5` int(11) DEFAULT NULL,
  `best_fielder_id6` int(11) DEFAULT NULL,
  `best_fielder_id7` int(11) DEFAULT NULL,
  `best_fielder_id8` int(11) DEFAULT NULL,
  `best_fielder_id9` int(11) DEFAULT NULL,
  PRIMARY KEY (`league_id`,`sub_league_id`,`year`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Create league_history_batting_stats table

There are a couple puzzles in this and the next two tables.  The columns ‘game_id’ and ‘team_id’ are hard to figure out.  In fact, I have not figured them out.  Their purpose, I’m sure, is to identify the sub_league.  Unfortunately, it doesn’t use a column called ‘sub_league’.  I haven’t been able to find any table that links either of these columns to sub_league.  So, there’s not much we can do with them.  Moreover, we’ll still have to create a view later on that sums the totals for each ‘team_id’ and ‘game_id’ pair.  I even posted about this on the OOTP Boards, but no one has chimed in yet.  Wishing doesn’t make it true.  Those columns most likely do not represent usable data.

‘split_id’ refers to stats accumulated against all pitchers, just righties, just lefties.  This is important at the player level.  At the league level, though, all split_id=0.

Also, this table conveniently tracks singles, ‘s’, as a column.  Would be nice if it did the same at the player level to save us the calculation in some statistics, but oh well.

Note that I added an AUTO_INCREMENT ID as PK and indexed ‘year’, ‘league_id’, and ‘split_id’.

CREATE TABLE `league_history_batting_stats` (
  `lhbs_id` int(11) NOT NULL AUTO_INCREMENT,    
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `game_id` int(11) DEFAULT NULL,
  `league_id` int(11) NOT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) DEFAULT NULL,
  `pa` int(11) DEFAULT NULL,
  `ab` int(11) DEFAULT NULL,
  `h` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `tb` int(11) DEFAULT NULL,
  `s` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `t` int(11) DEFAULT NULL,
  `hr` int(11) DEFAULT NULL,
  `sb` int(11) DEFAULT NULL,
  `cs` int(11) DEFAULT NULL,
  `rbi` int(11) DEFAULT NULL,
  `r` int(11) DEFAULT NULL,
  `bb` int(11) DEFAULT NULL,
  `ibb` int(11) DEFAULT NULL,
  `hp` int(11) DEFAULT NULL,
  `sh` int(11) DEFAULT NULL,
  `sf` int(11) DEFAULT NULL,
  `ci` int(11) DEFAULT NULL,
  `gdp` int(11) DEFAULT NULL,
  `g` int(11) DEFAULT NULL,
  `gs` int(11) DEFAULT NULL,
  `ebh` int(11) DEFAULT NULL,
  `pitches_seen` int(11) DEFAULT NULL,
  `avg` double DEFAULT NULL,
  `obp` double DEFAULT NULL,
  `slg` double DEFAULT NULL,
  `rc` double DEFAULT NULL,
  `rc27` double DEFAULT NULL,
  `iso` double DEFAULT NULL,
  `woba` double DEFAULT NULL,
  `ops` double DEFAULT NULL,
  `sbp` double DEFAULT NULL,
  `ws` double DEFAULT NULL,
  `kp` double DEFAULT NULL,
  `bbp` double DEFAULT NULL,
  `wpa` double DEFAULT NULL,
  `babip` double DEFAULT NULL,
  PRIMARY KEY (`lhbs_id`),
  INDEX `lhbs_ix1` (`year`),
  INDEX `lhbs_ix2` (`league_id`),
  INDEX `lhbs_ix3` (`split_id`)    
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Create  league_history_fielding_stats table

Similar to the above, but for fielding stats at league level.  I haven’t yet dug into what fielding stats I will be calculating, so I don’t have all that much to say about this table.

CREATE TABLE `league_history_fielding_stats` (
  `lhfs_id` int(11) NOT NULL AUTO_INCREMENT,    
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `league_id` int(11) NOT NULL,
  `sub_league_id` int(11) DEFAULT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) NOT NULL,
  `position` smallint(6) DEFAULT NULL,
  `g` int(11) DEFAULT NULL,
  `gs` int(11) DEFAULT NULL,
  `tc` int(11) DEFAULT NULL,
  `a` int(11) DEFAULT NULL,
  `po` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  `dp` int(11) DEFAULT NULL,
  `tp` int(11) DEFAULT NULL,
  `pb` int(11) DEFAULT NULL,
  `sba` int(11) DEFAULT NULL,
  `rto` int(11) DEFAULT NULL,
  `er` int(11) DEFAULT NULL,
  `ip` int(11) DEFAULT NULL,
  `ipf` int(11) DEFAULT NULL,
  `pct` double DEFAULT NULL,
  `range` double DEFAULT NULL,
  `rtop` double DEFAULT NULL,
  `cera` double DEFAULT NULL,
  `zr` double DEFAULT NULL,
  `plays` int(11) DEFAULT NULL,
  `plays_base` int(11) DEFAULT NULL,
  `roe` int(11) DEFAULT NULL,
  `eff` int(11) DEFAULT NULL,
  PRIMARY KEY (`lhfs_id`),
  INDEX `lhfs_ix1` (`year`),
  INDEX `lhfs_ix2` (`league_id`),
  INDEX `lhfs_ix3` (`split_id`) 
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Create league_history_pitching_stats table

This table may come in handy at some point – I haven’t done a lot of work on pitching stats yet.  But it does NOT come in handy for establishing a run environment for a league.  Why?  Because you can’t determine the number of outs!  It’s strange – as the player table has an outs column.  This table has an IP column, which would be OK if it weren’t an integer.  IP is usually tracked with a baseball-specific decimal number (x.y) where x is the number of complete innings pitched and y (0, 1, 2)  represent the outs of incomplete innings.  What are you supposed to do with only the integer?  Yes, well, apparently the column ‘ipf’  stands for Innings Pitched Fraction.  So, Outs = (3*IP) + IPF.  Anyway, I am sure I will find plenty of uses for this table in the future:

CREATE TABLE `league_history_pitching_stats` (
  `lhps_id` int(11)  NOT NULL AUTO_INCREMENT,    
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `game_id` int(11) DEFAULT NULL,
  `league_id` int(11) NOT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) DEFAULT NULL,
  `ab` int(11) DEFAULT NULL,
  `ip` int(11) DEFAULT NULL,
  `bf` int(11) DEFAULT NULL,
  `tb` int(11) DEFAULT NULL,
  `ha` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `rs` int(11) DEFAULT NULL,
  `bb` int(11) DEFAULT NULL,
  `r` int(11) DEFAULT NULL,
  `er` int(11) DEFAULT NULL,
  `gb` int(11) DEFAULT NULL,
  `fb` int(11) DEFAULT NULL,
  `pi` int(11) DEFAULT NULL,
  `ipf` int(11) DEFAULT NULL,
  `g` int(11) DEFAULT NULL,
  `gs` int(11) DEFAULT NULL,
  `w` int(11) DEFAULT NULL,
  `l` int(11) DEFAULT NULL,
  `s` int(11) DEFAULT NULL,
  `sa` int(11) DEFAULT NULL,
  `da` int(11) DEFAULT NULL,
  `sh` int(11) DEFAULT NULL,
  `sf` int(11) DEFAULT NULL,
  `ta` int(11) DEFAULT NULL,
  `hra` int(11) DEFAULT NULL,
  `bk` int(11) DEFAULT NULL,
  `ci` int(11) DEFAULT NULL,
  `iw` int(11) DEFAULT NULL,
  `wp` int(11) DEFAULT NULL,
  `hp` int(11) DEFAULT NULL,
  `gf` int(11) DEFAULT NULL,
  `dp` int(11) DEFAULT NULL,
  `qs` int(11) DEFAULT NULL,
  `svo` int(11) DEFAULT NULL,
  `bs` int(11) DEFAULT NULL,
  `ra` int(11) DEFAULT NULL,
  `ir` int(11) DEFAULT NULL,
  `irs` int(11) DEFAULT NULL,
  `cg` int(11) DEFAULT NULL,
  `sho` int(11) DEFAULT NULL,
  `sb` int(11) DEFAULT NULL,
  `cs` int(11) DEFAULT NULL,
  `hld` int(11) DEFAULT NULL,
  `r9` double DEFAULT NULL,
  `avg` double DEFAULT NULL,
  `obp` double DEFAULT NULL,
  `slg` double DEFAULT NULL,
  `ops` double DEFAULT NULL,
  `h9` double DEFAULT NULL,
  `k9` double DEFAULT NULL,
  `hr9` double DEFAULT NULL,
  `bb9` double DEFAULT NULL,
  `cgp` double DEFAULT NULL,
  `fip` double DEFAULT NULL,
  `qsp` double DEFAULT NULL,
  `winp` double DEFAULT NULL,
  `rsg` double DEFAULT NULL,
  `svp` double DEFAULT NULL,
  `bsvp` double DEFAULT NULL,
  `irsp` double DEFAULT NULL,
  `gfp` double DEFAULT NULL,
  `era` double DEFAULT NULL,
  `pig` double DEFAULT NULL,
  `ws` double DEFAULT NULL,
  `whip` double DEFAULT NULL,
  `gbfbp` double DEFAULT NULL,
  `kbb` double DEFAULT NULL,
  `babip` double DEFAULT NULL,
  `wpa` double DEFAULT NULL,
  PRIMARY KEY (`lhps_id`),
  INDEX `lhps_ix1` (`year`),
  INDEX `lhps_ix2` (`league_id`),
  INDEX `lhps_ix3` (`split_id`)     
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tables 2: Leagues, Sub_Leagues, and Divisions

cavebutterLeave a comment

One interesting feature in each of these tables is the ‘gender’ column.  Leaving aside the question of why leagues, subleagues, or divisions would need to be gendered (at least in English), it does beg the question of whether, perhaps OOTP will ever consider mixed or even female-only leagues.

Creating the leagues table

The leagues table has a LOT of columns.  Part of me thinks that I should figure out a way to truncate the data before loading it into the database because there’s just sooo many!  And I really only need the first several.  On the other hand, I would have to this every time I generated a data dump.  There will never be so many leagues that this table will become too large, so I will leave it alone for now.  Note that we are only interested in the first 4 columns:

CREATE TABLE `leagues` (
  `league_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `abbr` varchar(50) DEFAULT NULL,
  `nation_id` int(11) DEFAULT NULL,
  `language_id` int(11) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `historical_league` tinyint(4) DEFAULT NULL,
  `logo_file_name` varchar(200) DEFAULT NULL,
  `players_path` varchar(200) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `preferred_start_date` date DEFAULT NULL,
  `pitcher_award_name` varchar(50) DEFAULT NULL,
  `mvp_award_name` varchar(50) DEFAULT NULL,
  `rookie_award_name` varchar(50) DEFAULT NULL,
  `defense_award_name` varchar(50) DEFAULT NULL,
  `fictional_players` tinyint(4) DEFAULT NULL,
  `start_fantasy_draft` tinyint(4) DEFAULT NULL,
  `trading_deadline` tinyint(4) DEFAULT NULL,
  `winter_meetings` tinyint(4) DEFAULT NULL,
  `arbitration_offering` tinyint(4) DEFAULT NULL,
  `show_draft_pool` tinyint(4) DEFAULT NULL,
  `rosters_expanded` tinyint(4) DEFAULT NULL,
  `draft_date` date DEFAULT NULL,
  `rule_5_draft_date` date DEFAULT NULL,
  `roster_expand_date` date DEFAULT NULL,
  `trade_deadline_date` date DEFAULT NULL,
  `allstar_date` date DEFAULT NULL,
  `days_until_deadline` int(11) DEFAULT NULL,
  `next_draft_type` int(11) DEFAULT NULL,
  `parent_league_id` int(11) DEFAULT NULL,
  `league_state` smallint(6) DEFAULT NULL,
  `season_year` int(11) DEFAULT NULL,
  `historical_year` smallint(6) DEFAULT NULL,
  `league_level` smallint(6) DEFAULT NULL,
  `stats_detail` int(11) DEFAULT NULL,
  `historical_import_path` varchar(200) DEFAULT NULL,
  `foreigner_percentage` smallint(6) DEFAULT NULL,
  `was_ootp6` tinyint(4) DEFAULT NULL,
  `was_65` tinyint(4) DEFAULT NULL,
  `allstar_game` tinyint(4) DEFAULT NULL,
  `auto_schedule_allstar` tinyint(4) DEFAULT NULL,
  `allstar_team_id0` int(11) DEFAULT NULL,
  `allstar_team_id1` int(11) DEFAULT NULL,
  `schedule_file_1` varchar(200) DEFAULT NULL,
  `schedule_file_2` varchar(200) DEFAULT NULL,
  `rules_rule_5` tinyint(4) DEFAULT NULL,
  `rules_minor_league_options` tinyint(4) DEFAULT NULL,
  `rules_trading` tinyint(4) DEFAULT NULL,
  `rules_draft_pick_trading` tinyint(4) DEFAULT NULL,
  `rules_financials` tinyint(4) DEFAULT NULL,
  `rules_amateur_draft` tinyint(4) DEFAULT NULL,
  `rules_fa_compensation` tinyint(4) DEFAULT NULL,
  `rules_schedule_balanced` tinyint(4) DEFAULT NULL,
  `rules_schedule_inter_league` tinyint(4) DEFAULT NULL,
  `rules_schedule_force_start_day` tinyint(4) DEFAULT NULL,
  `rules_trades_other_leagues` tinyint(4) DEFAULT NULL,
  `rules_free_agents_from_other_leagues` tinyint(4) DEFAULT NULL,
  `rules_free_agents_leave_other_leagues` tinyint(4) DEFAULT NULL,
  `rules_allstar_game` tinyint(4) DEFAULT NULL,
  `rules_spring_training` tinyint(4) DEFAULT NULL,
  `rules_active_roster_limit` smallint(6) DEFAULT NULL,
  `rules_secondary_roster_limit` smallint(6) DEFAULT NULL,
  `rules_expanded_roster_limit` smallint(6) DEFAULT NULL,
  `rules_min_service_days` smallint(6) DEFAULT NULL,
  `rules_waiver_period_length` smallint(6) DEFAULT NULL,
  `rules_dfa_period_length` smallint(6) DEFAULT NULL,
  `rules_fa_minimum_years` smallint(6) DEFAULT NULL,
  `rules_salary_arbitration_minimum_years` smallint(6) DEFAULT NULL,
  `rules_minor_league_fa_minimum_years` smallint(6) DEFAULT NULL,
  `rules_foreigner_limit` smallint(6) DEFAULT NULL,
  `rules_foreigner_pitcher_limit` smallint(6) DEFAULT NULL,
  `rules_foreigner_hitter_limit` smallint(6) DEFAULT NULL,
  `rules_schedule_games_per_team` smallint(6) DEFAULT NULL,
  `rules_schedule_typical_series` smallint(6) DEFAULT NULL,
  `rules_schedule_preferred_start_day` smallint(6) DEFAULT NULL,
  `rules_amateur_draft_rounds` smallint(6) DEFAULT NULL,
  `rules_minimum_salary` int(11) DEFAULT NULL,
  `rules_salary_cap` int(11) DEFAULT NULL,
  `rules_player_salary0` int(11) DEFAULT NULL,
  `rules_player_salary1` int(11) DEFAULT NULL,
  `rules_player_salary2` int(11) DEFAULT NULL,
  `rules_player_salary3` int(11) DEFAULT NULL,
  `rules_player_salary4` int(11) DEFAULT NULL,
  `rules_player_salary5` int(11) DEFAULT NULL,
  `rules_player_salary6` int(11) DEFAULT NULL,
  `rules_player_salary7` int(11) DEFAULT NULL,
  `rules_average_coach_salary` int(11) DEFAULT NULL,
  `rules_average_attendance` int(11) DEFAULT NULL,
  `rules_average_national_media_contract` int(11) DEFAULT NULL,
  `rules_cash_maximum` int(11) DEFAULT NULL,
  `rules_average_ticket_price` double DEFAULT NULL,
  `rules_revenue_sharing` tinyint(4) DEFAULT NULL,
  `rules_national_media_contract_fixed` tinyint(4) DEFAULT NULL,
  `rules_owner_decides_budget` tinyint(4) DEFAULT NULL,
  `rules_schedule_auto_adjust_dates` tinyint(4) DEFAULT NULL,
  `rules_historical_import_rookies` tinyint(4) DEFAULT NULL,
  `avg_rating_contact` int(11) DEFAULT NULL,
  `avg_rating_gap` int(11) DEFAULT NULL,
  `avg_rating_power` int(11) DEFAULT NULL,
  `avg_rating_eye` int(11) DEFAULT NULL,
  `avg_rating_strikeouts` int(11) DEFAULT NULL,
  `avg_rating_stuff` int(11) DEFAULT NULL,
  `avg_rating_movement` int(11) DEFAULT NULL,
  `avg_rating_control` int(11) DEFAULT NULL,
  `avg_rating_fielding0` int(11) DEFAULT NULL,
  `avg_rating_fielding1` int(11) DEFAULT NULL,
  `avg_rating_fielding2` int(11) DEFAULT NULL,
  `avg_rating_fielding3` int(11) DEFAULT NULL,
  `avg_rating_fielding4` int(11) DEFAULT NULL,
  `avg_rating_fielding5` int(11) DEFAULT NULL,
  `avg_rating_fielding6` int(11) DEFAULT NULL,
  `avg_rating_fielding7` int(11) DEFAULT NULL,
  `avg_rating_fielding8` int(11) DEFAULT NULL,
  `avg_rating_fielding9` int(11) DEFAULT NULL,
  `avg_rating_overall` int(11) DEFAULT NULL,
  `avg_rating_age` double DEFAULT NULL,
  `league_totals_ab` int(11) DEFAULT NULL,
  `league_totals_h` int(11) DEFAULT NULL,
  `league_totals_d` int(11) DEFAULT NULL,
  `league_totals_t` int(11) DEFAULT NULL,
  `league_totals_hr` int(11) DEFAULT NULL,
  `league_totals_bb` int(11) DEFAULT NULL,
  `league_totals_hp` int(11) DEFAULT NULL,
  `league_totals_k` int(11) DEFAULT NULL,
  `league_totals_pa` int(11) DEFAULT NULL,
  `league_totals_babip` double DEFAULT NULL,
  `league_totals_mod_h` double DEFAULT NULL,
  `league_totals_mod_d` double DEFAULT NULL,
  `league_totals_mod_t` double DEFAULT NULL,
  `league_totals_mod_hr` double DEFAULT NULL,
  `league_totals_mod_bb` double DEFAULT NULL,
  `league_totals_mod_hp` double DEFAULT NULL,
  `league_totals_mod_k` double DEFAULT NULL,
  `league_totals_mod_babip` double DEFAULT NULL,
  `ml_equivalencies_avg` double DEFAULT NULL,
  `ml_equivalencies_hr` double DEFAULT NULL,
  `ml_equivalencies_eb` double DEFAULT NULL,
  `ml_equivalencies_bb` double DEFAULT NULL,
  `ml_equivalencies_k` double DEFAULT NULL,
  `ml_equivalencies_hp` double DEFAULT NULL,
  `player_creation_modifier_contact` double DEFAULT NULL,
  `player_creation_modifier_gap` double DEFAULT NULL,
  `player_creation_modifier_power` double DEFAULT NULL,
  `player_creation_modifier_eye` double DEFAULT NULL,
  `player_creation_modifier_strikeouts` double DEFAULT NULL,
  `player_creation_modifier_stuff` double DEFAULT NULL,
  `player_creation_modifier_movement` double DEFAULT NULL,
  `player_creation_modifier_control` double DEFAULT NULL,
  `player_creation_modifier_speed` double DEFAULT NULL,
  `player_creation_modifier_fielding` double DEFAULT NULL,
  `financial_coefficient` double DEFAULT NULL,
  `world_start_year` int(11) DEFAULT NULL,
  `current_date` date DEFAULT NULL,
  `background_color_id` varchar(8) DEFAULT NULL,
  `text_color_id` varchar(8) DEFAULT NULL,
  `scouting_coach_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`league_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Creating the sub_leagues table

The sub_leagues table is much more manageable.  It serves to name each league’s sub_leagues and attach them to a parent league.  Note here that I made a composite PK rather than adding an AUTO_INCREMENT:

CREATE TABLE `sub_leagues` (
  `league_id` int(11) NOT NULL,
  `sub_league_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `abbr` varchar(50) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `designated_hitter` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`league_id`,`sub_league_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Creating the divisions table

This one, too, is very straightforward.  Again, a composite primary key rather than an AUTO_INCREMENT:

CREATE TABLE `divisions` (
  `league_id` int(11) NOT NULL,
  `sub_league_id` int(11) NOT NULL,
  `division_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  PRIMARY KEY (`league_id`,`sub_league_id`,`division_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tables Post 1: Some Notes, Creating the DB, Cities and Nations Tables

cavebutterLeave a comment

There are a lot of tables that can be dumped from OOTP, and at this point I don’t need  nearly all of them.    With the exception of the ‘nations’ and ‘cities’ tables, they will all be focused on teams, leagues, and players.

A few important notes that I want to put up front so they don’t get lost in the code:

  • I am not using Foreign Keys (FK’s) in this database. There are two reasons for this.  First, probably due to ignorance, I was having a lot of trouble getting them set up.  There are only so many hours a person can research solutions to Error 1215 before he just throws up his hands and quits.  Second, as long as we’re properly indexed, FK’s shouldn’t have much impact on query performance.  As long as I can trust the data OOTP dumps to have referential integrity (and I do), then we should be OK.
  • I’ve added AUTO_INCREMENT ID’s to player_career and league_history tables.  Without them, the Primary Keys for these tables would be ridiculously composite, and still have PK violations when data is imported.  I will note these in the code.

 

Create the database. Easy enough.  This bit wipes the database and starts fresh:

DROP DATABASE IF EXISTS TEST1;
CREATE DATABASE TEST1;
USE TEST1;

 

Create nations table

I don’t have any plans to use this table right now, I can maybe see a potential use case when using a larger universe.

CREATE TABLE `nations` (
  `nation_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `short_name` varchar(50) DEFAULT NULL,
  `abbreviation` varchar(50) DEFAULT NULL,
  `demonym` varchar(50) DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `baseball_quality` int(11) DEFAULT NULL,
  `continent_id` int(11) DEFAULT NULL,
  `main_language_id` int(11) DEFAULT NULL,
  `quality_total` int(11) DEFAULT NULL,
  `capital_id` int(11) DEFAULT NULL,
  `use_hardcoded_ml_player_origins` tinyint(4) DEFAULT NULL,
  `this_is_the_usa` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`nation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Create cities table

Similar to above, though more potential use cases.  Particularly thinking about the owner goals in game where you are asked to sign a “home town player.”

CREATE TABLE `cities` (
  `city_id` int(11) NOT NULL,
  `nation_id` int(11) DEFAULT NULL,
  `state_id` int(11) DEFAULT NULL,
  `name` varchar(80) DEFAULT NULL,
  `abbreviation` varchar(10) DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `main_language_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Why We’re Here

cavebutterLeave a comment

I play a baseball sim called Out of the Park Baseball.  I have been playing it since 2005.  On and off for a couple of years, I have been fooling around with databases.  I’m not a programmer or a DBA, but I love data.  Ever since I learned how to do my first SELECT statement, something just clicked inside me.  I dabbled in Access here and there with the Lahman Database but never went too deep because I had other commitments, other interests, and no goal in mind for any database knowledge.

Earlier this year, a project for work required me to get a quick  understanding of Access and SQL Server.  I loved learning it and I suddenly saw a potential goal to direct my efforts into learning more about database management:  I would roll my own MySQL server to enhance my OOTP gameplay!

I would learn about remote database management; I would learn about complex JOINS, I would learn how to build complex queries in order to calculate advanced statistics.

Well, I started.  Then I got overwhelmed and I stopped.  Then I started again.  Stopped again.  I started again about a week ago- getting further than I had before, but still nowhere near my goal.  Just as I started to get frustrated again, it occurred to me that I need to slow down.

That’s what this blog is for: It will allow me to slow down, consider what I am doing, how I want to do it, give me a chance to learn, and to rubber-duck my way to success.

Comments will be open in case any of my imaginary readers want to chime in with advice or encouragement.

Since this is almost entirely for my own benefit, I am not going to explain much of the OOTP minutia or any but the more advanced statistics.

OK, here we go!