The data model

One of the goals of nfldb is for it to be simple to use. And for it to be simple to use, the data model should also be simple.

There are only 7 tables in the database. Here is a brief description of each:

• meta stores information about the database or about the state of the world. For example, it keeps track of the version of the database and the current week of the current NFL season.
• team stores a row for each team in the league. There is also a row that corresponds to an unknown team called UNK. This is used for players that are not on any current roster.
• player stores ephemeral data about players. Namely, it is the most current information about each player known by nfldb. The data is nearly a total copy of the data in nflgame's JSON player database.
• game stores a row for each NFL game in the preseason, regular season and postseason dating back to 2009. This includes games that are scheduled in the future but have not been played.
• drive stores a row for each drive in a single game.
• play stores a row for each play in a single drive.
• play_player stores a row for each player statistic in a single play.

You can get an overview of the entire database and the relationships between each table with the Entity-Relationship (ER) diagrams section of this page.

ER diagrams

Entity-Relationship (ER) diagrams are used to graphically represent the schema of a database. They show each entity, its attributes and the relationships between each entity. In the ER diagrams for nfldb, entities correspond to tables and attributes correspond to columns in a table.

An example of a relationship would be one-to-many between games and drives. Namely, for each game, there can be zero or more drives associated with that game and for each drive, there must be exactly one game associated with that drive.

Note that the ER diagrams do not contain derived fields. You can see documentation for each statistical category (including derived fields) on the statistical categories page.

There are two ER diagrams. The first is a condensed version that omits many of the statistical categories for plays and players. (Click on the image to get a full PDF of the ER diagram.)

The second is a full ER diagram, with all of the statistical categories:

If you're curious, the above ER diagrams are automatically generated with the nfldb-write-erwiz script using erwiz. If you'd like to use erwiz for your own projects, I host its documentation with examples since I was unable to find it elsewhere.