This is a working document where I'm trying to design what data tables would be needed for a hypothetical fantasy league site.
I'd love any feedback or opinions you might have (especially if they are technically informed!).
Here are some things that this architecture allows:
A league is the obvious structure to start off with.
However, my plan would be to push most of the settings down to the season level. Every season, your league can change its name or teams or scoring settings, and the full history is preserved.
So this table is really just for linking all of the seasons in one place.
Like with leagues, we're going to build multi-year continuity into teams right from the start. Team names and managers can change from year-to-year, and the franchise history is all preserved.
It's tempting to design a 1-to-1 relationship between teams and managers, but people will definitely want co-managers. Better build that from the get-go.
We could have a column on the MANAGERS table that grants commissioner power. But what if we wanted to have a commissioner without a team?
This one is optional. But, you could end up with some novel ideas: Roto leagues with divisions and a playoff?
This groups rotisserie and points together.
There would be a couple of period templates (daily, weekly) that would be pulled from for creating leagues. But leagues could customize the periods around uneven "week" sizes, shorter/longer playoffs, or ending early to avoid players resting at the end of the season.
For daily leagues, the start date and end date for a period would be the same day.
Some leagues may choose to have weekly matchups but allow daily lineup changes. This would start with the same daily/weekly templates as SEASON_SCORING_PERIODS, but could also be customized.
Application logic would need to make sure that each lineup period is contained within a single scoring period.
This is optional. We'll assume that a matchup can only have two teams, so we'll add those at columns. (We can call them "home team" and "away team" just for fun, but it's no different from "team1" and "team2.")
We could have separate tables for add/drops and trades, but my initial thought is to create a single TRANSACTIONS table that handles both.
Each transaction could have multiple rows of TRANSACTION_DETAILS. In theory, this could allow trades involving more than two teams.
The PLAYERS table would be global for all leagues. Having a "Position" column assumes that leagues couldn't set a custom cutoff for positions, which some sites allow.
It would need rebuilt every day to update teams (for trades) and occasional name changes.
This is another global table, built during each offseason when the league releases the schedule for the upcoming season.
This is mostly just to help users with setting their lineups, to know who is playing.
Each morning, we would want to check and update the current day's schedule to catch rescheduled games.
These are global tables that would ideally be updated for the current day every 10-15 minutes.
An additional update (full rebuild?) would run at the end of the scoring period to catch any stat adjustments.
If a player has multiple games on the same day, we'll compress the stats for all games into a single row per player.
Does IDP need a separate table from team defense?
One more global table. This is your standard user authentication system with stuff like email addresses for password resets.
Good database design says you shouldn't store data that can be computed from more basic data. If you do, you could end up with data that's out of sync because the computed values didn't get updated.
However, there's going to be some frequently accessed computed data that maybe we want to have ready without computing every time. Here's what I'd maybe consider storing.
We can calculate a team's stats for a date range by matching a team's LINEUPS that fall within the SCORING_PERIOD, and then summing the stats from DAILY_STATS_[SPORT] for active players.
For head-to-head scoring, we need to get the team stats by period (above), then use the MATCHUPS table to determine wins and losses.
For rotisserie, we need to get the team stats for the whole season (above) and then calculating rotisserie points.
For head-to-head, we need to get the win-loss records (above) and rank by winning percentage.
User winning percentage, trophies, etc.
A fantasy league needs scheduled jobs to process various regular events. Here's what I've thought of so far:
If you're still tracking your draft with a custom spreadsheet or even just pen and paper, you need to try DraftKick.
It is packed with features to help you succeed on draft day:
It's completely free to try out!
Hi,
I'm Mays. I've been playing fantasy since I was in high school (over two decades ago).
My speciality has always been player valuation—converting player stats into rankings and salary values. VBD for fantasy football? Rotisserie z-scores? We go way back. In 2009, I started Last Player Picked, a site that generated fantasy values customized for your league.
You can find me on Twitter at @MaysCopeland or email me at [email protected].