I’ve had the dream of building a better fantasy sports site for a while now, one where I could do all sorts of wacky data manipulation and feature addition. Unfortunately, getting such a site up and running seems to have two conflicting steps: 1. I want to have something up and running that I can dig into relatively quickly, but 2. I want it to be incredibly flexible and extensible. So, I’ve decided that #1 is the more important one, but that doesn’t mean I can’t continue to think about how to do #2. And a lot of #2 is going to require DB schema changes, because obviously if you want a site to be more dynamic/flexible and to allow the users to control more things, then you need more fields in the database to store these changes.
The specific example this time is in dealing with what statistics to track and how to score them. Most popular fantasy leagues only track a very small subset of the available stats on a given sport. In football, it’s typically a list like:
- QB: pass yds, pass TDs, interceptions
- RB/WR/TE: rec yds, rec TDs
- QB/RB/WR/TE: rush yds, rush TDs, fumbles lost, 2pt conversions
- K: FG made/missed in specified ranges (0-19 yds, 20-29 yds, … up to 50 yards or more)
- DEF: fumble recoveries, defensive touchdowns, sacks, interceptions
But there are still plenty of other stats out there, especially in leagues that use different positions (like ones that use IDPs instead of team defenses). So, my ultimate end would be to have a flexible place to store each statistic and its info, though for now I’m happy with something that just the ones I need to run my leagues.
The question is, how exactly would such a structure look in the database? Keeping in mind that some of these stats are fundamentally different, and some have some scoring quirks, I’ve come up with three separate stat “types”: the Range Stat, the Rate Stat, and the Rate Stat With Bonuses.
The Range Stat would be something like “Points Allowed” for a team defense or “FG Made” for a kicker. I think, rather than having specific stats like “FG1-19″ (number of field goals made that were between 1 and 19 yards), I should just be able to say “he made an 18 yard field goal” and let the scoring rules for each individual league adjust it accordingly, instead of shoe-horning it into the FG1-19 stat for one league and the FG1-29 for another. Same thing with defensive points allowed, I’d like to just be able to enter each team’s scores for a game and then have it slot accordingly.
Rate Stats are the bread and butter of fantasy. They are the “6 pts per rush TD” and “0.1 pts per rush yd” stats. There’s not much magic here, really.
Rate Stats With Bonuses are less common, but useful. Some leagues choose to offer a bonus for big games like instead of just “0.1 pts per rush yd” they’ll have “0.1 pts per rush yd, +5 pts at 100 yds”. Not a whole lot of magic here either, as the bonus is applied during the score evaluation and not stored in the database itself.
But the question is, how do I represent these in the DB without having static columns for each possible stat? Is that possible? I’d like to be able to potentially add new stats without having to alter table structures. I’d rather not have to just have a huge table with nothing but default values for things that I’m not using at the moment. I’m wondering if there’s some sort of generic pattern for doing this or maybe I just haven’t thought hard enough about it. I’ll post details about the current schema in a later post, perhaps.