Designing Fantasy League Architecture

November 5, 2023

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!).

Unique Features

Here are some things that this architecture allows:

LEAGUES

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.

SEASONS

FRANCHISES

TEAMS

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.

MANAGERS

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.

COMMISSIONERS

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?

DIVISIONS

This one is optional. But, you could end up with some novel ideas: Roto leagues with divisions and a playoff?

CATEGORIES

This groups rotisserie and points together.

POSITIONS

SCORING_PERIODS

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.

LINEUP_PERIODS

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.

MATCHUPS

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.")

LINEUPS

TRANSACTIONS

We could have separate tables for add/drops and trades, but my initial thought is to create a single TRANSACTIONS table that handles both.

TRANSACTION_DETAILS

Each transaction could have multiple rows of TRANSACTION_DETAILS. In theory, this could allow trades involving more than two teams.

TRANSACTION_VETOES

PLAYERS

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.

SCHEDULE

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.

DAILY_STATS_HITTING, DAILY_STATS_PITCHING, DAILY_STATS_OFFENSE, DAILY_STATS_KICKERS, DAILY_STATS_DEFENSE, DAILY_STATS_BASKETBALL

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?

USERS

One more global table. This is your standard user authentication system with stuff like email addresses for password resets.

Computed Data

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.

Team Stats By Date

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.

Team W/L Records

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.

Standings

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.

Current Rosters

User History

User winning percentage, trophies, etc.

Time Triggers

A fantasy league needs scheduled jobs to process various regular events. Here's what I've thought of so far:

Season Creation

End of Day

The fantasy football draft assistant for a perfect draft. Every time.

Stop stressing over who to pick. Pick confidently with actionable rankings tailored to your league.

Auto-sync with Yahoo, ESPN, CBS, and Sleeper

DraftKick data sits inside your draft room.

Know exactly when to strike

See projected availability and never miss your targets.

Built for any league format

Keepers, auctions, custom scoring—covered.

Data-driven rankings

Objective rankings from the best projections.

Fully customizable

Edit projections, adjust settings, make it yours.

Lightning fast

Faster than dealing with clunky spreadsheets.

Open DraftKick app

Hi, I'm Mays.

I've been playing and building fantasy tools for over two decades. I started sharing my insights at Last Player Picked in 2008 and have helped countless fantasy players along the way.

DraftKick is the latest evolution of those tools. It combines best-in-class valuation algorithms with a fast, easy-to-use interface that gives you a clear edge on draft day.

DraftKick and its in-season companion MVP are the primary products of Last Player Picked, covering your team from draft day through the rest of the season.

You can find me on Twitter at @MaysCopeland or email me at [email protected].