Get Your Own NFL Database

I’m making a Microsoft SQL Server NFL database available that contains all play by play data from the 2009-2019 seasons! If you want to be able to do your own analysis or just discover statistical information about players you can start here by downloading a copy of the data an restoring it to a SQL Server database. I suggest using Microsoft SQL Server Express which is free to download and use. The backup of the database can be downloaded here.

NFL Play by Play Database Overview

A PDF of the database diagram is available here. We will cover the data in each table over the content of this post.

NFL Database Diagram
NFL Database Diagram

NFL Game Data

NFL Game Data
NFL Game Data

Included in the database is a table that contains all the games for regular and postseason. Each game record has the score at the end of each quarter.

NFL Game Drive Data

Each NFL Game is composed of drives. The drives are in the Drive table and belong to a game. The pos_team is the team with the ball on that drive. Each drive has a result of the drive such as Punt, Safety, End of Game, etc.

NFL Drive Data
NFL Drive Data

NFL Play by Play Data

The Play table records the result of each play in the game. Each play belongs to a drive and as above each drive to a game. The Play data contains the starting yard line, down, yards to go, etc.

NFL Play Table
NFL Play Table

NFL Player Data

The Player table contains the list of NFL players for the 2009-2019 seasons. If a player is no longer active their team will be UNK for unknown and status will be Unkown. The player team will always be their most current team. There is still history if they played for another team. We will get to an example of that in the introduction of the next table Play_Player.

NFL Player Table
NFL Player Table

NFL Play_Player Data

NFL Play Player Table
NFL Play Player Table

Each play can have numerous players involved. The Play_Player table will record the results of each play by player. An example would be if we select the following game (eid), dive and play:

SELECT eid, drive_id, play_id, player_id, team, passing_att, passing_cmp, passing_cmp_air_yds, passing_tds,
receiving_rec, receiving_tar, receiving_tds, receiving_yds, receiving_yac_yds
FROM play_player
WHERE eid = 2016012400 and drive_id=2 and play_id = 467

In this play, Peyton Manning passed to TE Owen Daniels. If you execute the query you will see 2 records of data that show the pass attempt and results.

Manning Pass Play Results
Manning Pass Play Results

And to follow up on the previous issue of what team was a player with, we can see Manning was playing for DEN during this play.

NFL Fantasy_data

The fantasy data table that is included in the backup of the database is calculated using full point PPR scoring. In a later post, I will show how to add a column and calculate alternate scoring. All the main elements that go into a fantasy score all stored in the table so it is only a matter of a calculation to get an alternate score.

NFL Fantasy Data
NFL Fantasy Data

NFL Agg_Game Table

The Agg_Game table is an aggregate of the play table with fewer fields. It is meant to summarize the result of the game for each side of the ball.

NFL Agg_Game Table
NFL Agg_Game Table

NFL Agg_Play Table

The Agg_Play table is an aggregate of the play_player data. The play_player table has more detailed information than the play table. But the agg_play table summarizes the results for all players for a single play.

NFL Agg_Play Table
NFL Agg_Play Table

NFL Agg_Play_Player

The Agg_Play_player table summarizes the Play_Player data for each game. This will result in a single record for a player per game with details of all the player’s statistics for the single game.

NFL Agg_Play_Player
NFL Agg_Play_Player

NFL Play by Play Database Summary

That is an overview of the data contained in the database download file. In the next post, I will provide directions on downloading and restoring the NFL Play by Play database to SQL Server Express. Get your database up and running now in just 5 quick steps!

Judy Adabie
  • Judy Adabie
  • I love sports and data!  My background is in .Net development and SQL Server.  I earned my MS in Analytics from Villanova in 2017.  I hope you find some great insights from the play by play data.

4 Comments

  • I’ll immediately take hold of your rss as I can’t find your
    e-mail subscription hyperlink or e-newsletter service. Do you have any?
    Kindly let me know in order that I may subscribe.
    Thanks.

  • My partner and I absolutely love your blog
    and find almost all of your post’s to be just what I’m looking for.
    Do you offer guest writers to write content in your
    case? I wouldn’t mind writing a post or elaborating on most of the subjects you write about here.
    Again, awesome weblog!

  • My brother recommended I might like this blog.
    He was entirely right. This post truly made my
    day. You can not imagine just how much time I had spent for this information! Thanks!

  • What’s up, just wanted to tell you, I loved this blog post.
    It was funny. Keep on posting!

Leave a Comment

Your email address will not be published. Required fields are marked *