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;