<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Pigskin Lab</title>
    <description>The latest articles on DEV Community by Pigskin Lab (@pigskinlab).</description>
    <link>https://dev.to/pigskinlab</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4257857%2Ff7270b7d-d618-4d63-bc40-97275d9266b6.png</url>
      <title>DEV Community: Pigskin Lab</title>
      <link>https://dev.to/pigskinlab</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/pigskinlab"/>
    <language>en</language>
    <item>
      <title>Blank is not zero: adding FCS football to a stats site without pretending the data is complete</title>
      <dc:creator>Pigskin Lab</dc:creator>
      <pubDate>Sat, 21 Nov 2026 19:42:37 +0000</pubDate>
      <link>https://dev.to/pigskinlab/blank-is-not-zero-adding-fcs-football-to-a-stats-site-without-pretending-the-data-is-complete-4afk</link>
      <guid>https://dev.to/pigskinlab/blank-is-not-zero-adding-fcs-football-to-a-stats-site-without-pretending-the-data-is-complete-4afk</guid>
      <description>&lt;p&gt;I run Pigskin Lab, a football stats site where you ask questions in plain English. This week I added FCS, the second tier of Division I college football (North Dakota State, Montana, South Dakota State and about 125 other schools). The hard part wasn't loading the data. It was being honest about what the data doesn't have.&lt;/p&gt;

&lt;h2&gt;
  
  
  What the source actually has
&lt;/h2&gt;

&lt;p&gt;College data comes from the CollegeFootballData API. Before writing any UI I measured what it holds for FCS, season by season:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Schedules and scores go back to 2004.&lt;/li&gt;
&lt;li&gt;Full box scores for FCS-vs-FCS games start in 2022. 2021 has only fragments. Before that, the only FCS box scores are games against FBS schools.&lt;/li&gt;
&lt;li&gt;Defensive player stats exist for 2022–2025 only in those games against FBS schools. In 2026 they're in 651 of 655 FCS-only box scores.&lt;/li&gt;
&lt;li&gt;There's no FCS play-by-play loaded, so no EPA, success rate or targets.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;If you show that as a normal stats table, a linebacker with eleven games and two of them against FBS opponents looks like he made 9 tackles all season. That's worse than showing nothing.&lt;/p&gt;

&lt;h2&gt;
  
  
  Sibling tables, not one big table
&lt;/h2&gt;

&lt;p&gt;The FBS tables already had about 400 queries reading them without any division filter. Folding FCS rows into them would have changed every one of those answers. So FCS rows live in sibling tables with the same columns (&lt;code&gt;cfb_games_fcs&lt;/code&gt;, &lt;code&gt;cfb_player_seasons_fcs&lt;/code&gt; and so on), and views union the two with a &lt;code&gt;division&lt;/code&gt; column added:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;OR&lt;/span&gt; &lt;span class="k"&gt;REPLACE&lt;/span&gt; &lt;span class="k"&gt;VIEW&lt;/span&gt; &lt;span class="n"&gt;cfb_games_all&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt;
  &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'fbs'&lt;/span&gt;&lt;span class="o"&gt;::&lt;/span&gt;&lt;span class="kt"&gt;text&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;division&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;cfb_games&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;
  &lt;span class="k"&gt;UNION&lt;/span&gt; &lt;span class="k"&gt;ALL&lt;/span&gt;
  &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'fcs'&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;cfb_games_fcs&lt;/span&gt; &lt;span class="n"&gt;f&lt;/span&gt;
  &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;EXISTS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;cfb_games&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt; &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;game_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;game_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;An FBS-vs-FCS game can exist on both sides; the FBS row wins. Old queries didn't change; new FCS-aware pages read the &lt;code&gt;_all&lt;/code&gt; views. (The real views list their columns; this is the shape.)&lt;/p&gt;

&lt;h2&gt;
  
  
  A table that records what each season has
&lt;/h2&gt;

&lt;p&gt;The important piece is small: &lt;code&gt;cfb_fcs_seasons&lt;/code&gt;, one row per season, filled by the loader after it measures the data. It records whether box scores exist and whether defense is complete, using a 90% rule on box-score coverage.&lt;/p&gt;

&lt;p&gt;Every FCS view reads one helper that turns that row into a sentence, so the caveat is the same everywhere:&lt;/p&gt;

&lt;blockquote&gt;
&lt;p&gt;FCS: player stats start in 2022; 2022–25 defense counts games vs FBS opponents only; no targets, EPA or play charts (no FCS play-by-play).&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;A partial-season defensive total gets an asterisk and "games vs FBS opponents only (N games)". There are no FCS defensive leaderboards in a partial season at all, because ranking incomplete numbers against complete ones is the thing I'm trying not to do.&lt;/p&gt;

&lt;h2&gt;
  
  
  Checking it against the source
&lt;/h2&gt;

&lt;p&gt;The loader adds up box scores into season totals, which is exactly where off-by-one-game bugs hide. A test compares every FCS player's passing, rushing and receiving yards, passing touchdowns and catches for 2022–2025 against CFBD's own season-stats endpoint: 26,142 numbers, 0 different on the last run. When CFBD corrects a box score, that test is how I find out.&lt;/p&gt;

&lt;h2&gt;
  
  
  What I'd tell someone doing the same
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;Measure the source before you design the page. It's tempting to assume the smaller division looks like the bigger one with fewer rows; it doesn't.&lt;/li&gt;
&lt;li&gt;Store what's missing as data, not as a comment in the code. A table of "what this season has" is cheap and every page can read it.&lt;/li&gt;
&lt;li&gt;Write the caveat once and reuse it, so a school page, a player page and an answer never word the same gap differently.&lt;/li&gt;
&lt;li&gt;Blank is not zero. If you can't tell them apart in your UI, your users can't either.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;If you want to see how it reads in practice, here's an FCS school page: &lt;a href="https://pigskinlab.com/cfb/team/north-dakota-state?ref=devto" rel="noopener noreferrer"&gt;https://pigskinlab.com/cfb/team/north-dakota-state?ref=devto&lt;/a&gt; (free during the beta).&lt;/p&gt;

&lt;p&gt;College data: CollegeFootballData.com. NFL data: nflverse.&lt;/p&gt;

&lt;p&gt;&lt;em&gt;Drafted with AI assistance and checked against the project's code and docs.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>postgres</category>
      <category>dataengineering</category>
      <category>sql</category>
      <category>webdev</category>
    </item>
  </channel>
</rss>
