OOTP MySQL Reference
 

For anyone interested in rolling their own MySQL database to play around with OOTP or advanced stats in general, I am working on a project that documents my efforts to do just that. In the process, I'll be writing about various elements of OOTP tables and the nuts and bolts of sabrmetrics.

This project is really for my own benefit as an aid to better understanding MySQL and advanced statistics, but figured I would link to it here in case there are any other nerds interested in the same. I don't pretend to be an expert - and this will likely be more a catalog of my mistakes as I stumble towards understanding than a definitive guide. Well, can't sell it any better than that. :)

MySQL, MyStruggle

Fun project. Don't make the mistake of thinking the OOTP data dump is a well thought out database. There's a lot of good information in it, but you're going to pull your hair out trying to make sense of it where no sense is to be made. You can often assume that if something doesn't make sense, like a game_id column in players_career stats, then it's just not reporting anything useful.

I took a quick glance through your blog and have the following couple of notes:

• You say you're going to use the parks table to adjust your statistics using the park factors from that table. You will get bad results doing this. Those are in-game configuration settings, not calculated park factors based on actual results. They don't change from year to year. If you want to know how to adjust the stats, you have to calculate park factors based on home/road splits and save that off somewhere.
  
• Players_career_fielding_stats:
  • rto = Runners Thrown Out (for catchers)
  • ipf = innings played fraction. Innings played = (3*IP + IPF)/3. The pitching stats table has this, too, but for whatever reason also includes an outs column that does the 3*IP+IPF calculation for you.
  • Detailed UZR data isn't anywhere in the data dump

Thanks so much for the notes!

Too bad about the park factors. I really thought I had found a shortcut.

I've been doing something similar, let me know if you want to compare some notes.

My main peeve right now is that there are no lookup tables, example, an at bat will have a result of 9, but nothing to tell you what 9 means. It does this all over the place, I've jotted these down somewhere I need to find them and once I do I'll share.