Data Modeling: Teams, Seasons, Gameweeks & Fixtures

Premier League Predictor: Astro

Chapter 2 · Data Modeling: Teams, Seasons, Gameweeks & Fixtures

Everything else in this course — the admin tools, the fixture-entry UI, predictions, results, both league tables, promotion and relegation — sits on top of the schema this chapter builds. Five real tables: teams, seasons, season_teams, gameweeks, and fixtures, written as raw SQL rather than through an ORM, since better-sqlite3 is a thin, direct SQLite driver with no object-relational layer sitting on top of it. Predictions themselves are deliberately left out of this chapter — that's a genuinely separate concern, covered on its own in Chapter 5.

Team & Season: What Persists vs. What's Real Per Season

A real football club doesn't stop existing the season it gets relegated. teams holds every team this app has ever tracked — promoted, relegated, or currently playing, it doesn't matter — because a team's own identity, name, and history are genuinely permanent facts, not something tied to a single season.

seasons represents one real Premier League season — "2026/27," with a start date, an end date, and an is_current flag so the app always knows which season is the active one.

season_teams: The 20 Teams Actually Competing

Which 20 teams are actually in the Premier League changes every season — three go down, three come up. That's a genuinely different fact from "this team exists," so it gets its own table rather than a column on teams itself: season_teams, a join table recording which teams are competing in which season.

This is exactly what Chapter 3 and Chapter 9 operate on

Chapter 3's own admin tooling for managing "the 20 competing teams each season" is really just CRUD against season_teams rows for the current season. Chapter 9's promotion/relegation logic, at the end of a season, is the same table on the other end: remove the bottom three teams' own season_teams rows for next season, add three new rows for the promoted teams. teams itself never changes in either operation — only which teams are marked as competing in a given season does.

Gameweek & Fixture: The Real Schema

A real season has 38 gameweeks; each gameweek has (normally) 10 fixtures, one per pair of the 20 competing teams. The whole schema, written once as plain SQL and run at startup:

-- src/lib/schema.sql
CREATE TABLE IF NOT EXISTS teams (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  short_name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS seasons (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,              -- e.g. "2026/27"
  start_date TEXT NOT NULL,               -- ISO 8601, e.g. "2026-08-15"
  end_date TEXT NOT NULL,
  is_current INTEGER NOT NULL DEFAULT 0   -- SQLite has no BOOLEAN type; 0 or 1
);

CREATE TABLE IF NOT EXISTS season_teams (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  season_id INTEGER NOT NULL REFERENCES seasons(id),
  team_id INTEGER NOT NULL REFERENCES teams(id),
  UNIQUE (season_id, team_id)
);

CREATE TABLE IF NOT EXISTS gameweeks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  season_id INTEGER NOT NULL REFERENCES seasons(id),
  number INTEGER NOT NULL,                -- 1 through 38
  UNIQUE (season_id, number)
);

CREATE TABLE IF NOT EXISTS fixtures (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  gameweek_id INTEGER NOT NULL REFERENCES gameweeks(id),
  home_team_id INTEGER NOT NULL REFERENCES teams(id),
  away_team_id INTEGER NOT NULL REFERENCES teams(id),
  kickoff_time TEXT,                      -- ISO 8601, nullable
  home_score INTEGER,                     -- nullable until played
  away_score INTEGER,
  status TEXT NOT NULL DEFAULT 'scheduled',
  CHECK (home_team_id != away_team_id)
);

Why this stack never runs into the FastAPI sibling's own foreign-key problem

The FastAPI & PostgreSQL course had to resolve a real ambiguity: SQLAlchemy needs an explicit foreign_keys=[...] argument on home_team and away_team, because its ORM layer tries to infer which relationship follows which foreign key, and two foreign keys pointing at the same table make that inference genuinely ambiguous. That whole problem simply doesn't exist here. home_team_id and away_team_id are just two plain columns, each with its own explicit REFERENCES teams(id) clause, read directly off the row with no relationship-inference step sitting in between. Nothing is being guessed, so there's nothing to disambiguate — a genuinely different consequence of a genuinely different kind of database layer, not a coincidence.

SQLite ignores foreign keys unless a pragma turns them on

Every REFERENCES clause above is declared correctly, but SQLite — unlike PostgreSQL, which always enforces foreign keys by default — genuinely ignores foreign key constraints on every connection until PRAGMA foreign_keys = ON is explicitly run against that connection. Skip it, and inserting a fixture with a home_team_id that doesn't exist in teams at all will silently succeed, with the broken reference only surfacing later, whenever something tries to join against the missing row. This is set once, in the connection module below, before the schema is even created.

The Database Connection Module

One shared module opens the database, turns on foreign key enforcement, and creates every table above if it doesn't already exist — imported by every API route that needs it, rather than each route managing its own connection:

// src/lib/db.ts
import Database from 'better-sqlite3';
import { readFileSync } from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const dbPath = path.join(process.cwd(), 'data', 'pl_predictor.db');

export const db = new Database(dbPath);

// Off by default in SQLite — must be set per connection, before anything else runs
db.pragma('foreign_keys = ON');

const schema = readFileSync(path.join(__dirname, 'schema.sql'), 'utf-8');
db.exec(schema);

better-sqlite3 is genuinely synchronous — there's no await inside it

Most Node.js database drivers are asynchronous, returning promises for every query. better-sqlite3 deliberately isn't — db.prepare(sql).get(...), .all(...), and .run(...) all execute immediately and return a real value directly, with no promise and no await anywhere in the database layer itself. Every API route built from Chapter 3 onward still declares its GET/POST function as async, since that's what Astro's own APIRoute type expects — but the actual database calls inside those functions run synchronously underneath, finishing before the next line of code even starts.

Nullable Scores: A Fixture Exists Before It's Played

home_score and away_score are both left off NOT NULL, and status defaults to 'scheduled'. A fixture row is created the moment it's added to a gameweek — Chapter 4's own click-to-pair UI — long before it's actually played. Entering a result in Chapter 6 isn't an INSERT at all; it's an UPDATE against a fixture row that has existed since the gameweek was first set up, filling in the two score columns and flipping status to 'played'.

One real constraint this schema can't enforce on its own

CHECK (home_team_id != away_team_id) stops a team from playing itself — that's a genuine per-row check SQLite can enforce directly, the same as it would in PostgreSQL. A team appearing twice in the same gameweek — once as home in one fixture, once as away in a different fixture — is a different kind of rule entirely: it spans multiple rows, comparing every fixture in a gameweek against every other one. A single-row CHECK constraint can't express that in SQLite any more than it could in Postgres. This schema leaves it to the application layer instead: Chapter 4's own fixture-entry UI removes a team from the list of clickable options the moment it's already been used somewhere in that gameweek, so the rule is enforced by what the interface lets you click, not by the database refusing an insert after the fact.

Real fixtures do get moved between gameweeks — this schema already allows it

Real Premier League fixtures are rescheduled sometimes — cup replays, European commitments, TV moves. Because gameweek_id is just a plain column on fixtures, moving a fixture to a different gameweek is technically nothing more than updating one column on one row — the schema itself puts up no resistance. What's still genuinely undecided is the actual workflow for doing that safely in this app (should predictions already made against the old gameweek carry over, get cleared, or something else?) — a real open question, not something this chapter needs to resolve to keep building.

TypeScript Row Types

Following the same two-layer discipline this course's own siblings use — the schema describes what's actually stored, a set of plain interfaces describes what a row looks like once it comes back out of the database:

// src/lib/types.ts
export interface TeamRow {
  id: number;
  name: string;
  short_name: string;
}

export interface SeasonRow {
  id: number;
  name: string;
  start_date: string;
  end_date: string;
  is_current: number;   // 0 or 1 — SQLite has no real boolean column type
}

export interface FixtureRow {
  id: number;
  gameweek_id: number;
  home_team_id: number;
  away_team_id: number;
  kickoff_time: string | null;
  home_score: number | null;
  away_score: number | null;
  status: string;
}

is_current stays a raw number at this layer, matching exactly what SQLite actually stores and hands back. Any code that wants a real boolean converts it explicitly — Boolean(season.is_current) — rather than pretending the database column was ever a true boolean type to begin with.

Where This Course Is Headed

Managing the 20 competing teams each season — real CRUD against season_teams (Chapter 3); the fast click-to-pair fixture-entry UI that creates fixtures rows against this chapter's own schema (Chapter 4); recording all four prediction sources per fixture (Chapter 5); entering results — the real UPDATE this chapter set up (Chapter 6); the real league table (Chapter 7); the prediction league table (Chapter 8); and promotion/relegation, operating on season_teams exactly as previewed above (Chapter 9).

Hands-On Exercises

Exercise 1

Explain why "which 20 teams are competing this season" is modeled as a separate season_teams table rather than a column on teams itself, and name the two later chapters that operate directly on season_teams rows.

📄 View solution

Exercise 2

Explain why fixtures' home_team_id and away_team_id columns don't need anything like SQLAlchemy's foreign_keys=[...] argument in this raw-SQL, better-sqlite3 schema, even though both columns reference the same teams table.

📄 View solution

Exercise 3

Demonstrate the real consequence of forgetting PRAGMA foreign_keys = ON: insert a fixture row referencing a team ID that doesn't exist, once against a database connection where the pragma was never set, and once against one where it was — then write down exactly what happens in each case.

📄 View solution

Chapter 2 Quick Reference

teams — every team ever tracked, permanent regardless of promotion/relegation
seasons — one real Premier League season, with an is_current flag stored as 0/1
season_teams — the join table recording which 20 teams compete in a given season; Chapters 3 and 9 both operate directly on it
gameweeks — 1 through 38 per season, unique per (season_id, number)
fixtures — home_team_id/away_team_id both reference teams(id) directly, with no relationship-inference layer to disambiguate, unlike the SQLAlchemy sibling
Real gotcha — SQLite ignores foreign keys entirely until PRAGMA foreign_keys = ON is run per connection, set once in db.ts before the schema is created
better-sqlite3 is synchronous — .get()/.all()/.run() return real values directly, no await needed inside the database layer itself
Nullable home_score/away_score — a fixture exists before it's played; Chapter 6 fills these in via UPDATE, not INSERT
Real limit — "no team plays twice in one gameweek" is enforced by the UI (Chapter 4), not a database constraint, unlike "a team can't play itself" (CHECK)
Still open — the real workflow for moving a fixture to a different gameweek after it's already been entered
Next chapter: Admin — managing the 20 competing teams each season