Data Modeling: Teams, Seasons, Gameweeks & Fixtures

Premier League Predictor: FastAPI & PostgreSQL

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: Team, Season, SeasonTeam, Gameweek, and Fixture. 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. Team 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.

Season 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.

SeasonTeam: 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 Team itself: SeasonTeam, 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 SeasonTeam 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 SeasonTeam rows for next season, add three new rows for the promoted teams. Team itself never changes in either operation — only which teams are marked as competing in a given season does.

Gameweek & Fixture: The Real Schedule

A real season has 38 gameweeks; each gameweek has (normally) 10 fixtures, one per pair of the 20 competing teams. Both get their own table:

# models.py
from sqlalchemy import (
    Column, Integer, String, Boolean, Date, DateTime,
    ForeignKey, UniqueConstraint, CheckConstraint, func
)
from sqlalchemy.orm import relationship
from database import Base


class Team(Base):
    __tablename__ = "teams"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, unique=True, nullable=False)
    short_name = Column(String, nullable=False)


class Season(Base):
    __tablename__ = "seasons"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, unique=True, nullable=False)   # e.g. "2026/27"
    start_date = Column(Date, nullable=False)
    end_date = Column(Date, nullable=False)
    is_current = Column(Boolean, nullable=False, default=False)

    gameweeks = relationship("Gameweek", back_populates="season")
    season_teams = relationship("SeasonTeam", back_populates="season")


class SeasonTeam(Base):
    __tablename__ = "season_teams"
    __table_args__ = (
        UniqueConstraint("season_id", "team_id", name="uq_season_team"),
    )

    id = Column(Integer, primary_key=True, index=True)
    season_id = Column(Integer, ForeignKey("seasons.id"), nullable=False)
    team_id = Column(Integer, ForeignKey("teams.id"), nullable=False)

    season = relationship("Season", back_populates="season_teams")
    team = relationship("Team")


class Gameweek(Base):
    __tablename__ = "gameweeks"
    __table_args__ = (
        UniqueConstraint("season_id", "number", name="uq_season_gameweek_number"),
    )

    id = Column(Integer, primary_key=True, index=True)
    season_id = Column(Integer, ForeignKey("seasons.id"), nullable=False)
    number = Column(Integer, nullable=False)   # 1 through 38

    season = relationship("Season", back_populates="gameweeks")
    fixtures = relationship("Fixture", back_populates="gameweek")


class Fixture(Base):
    __tablename__ = "fixtures"
    __table_args__ = (
        CheckConstraint("home_team_id != away_team_id", name="ck_fixture_teams_differ"),
    )

    id = Column(Integer, primary_key=True, index=True)
    gameweek_id = Column(Integer, ForeignKey("gameweeks.id"), nullable=False)
    home_team_id = Column(Integer, ForeignKey("teams.id"), nullable=False)
    away_team_id = Column(Integer, ForeignKey("teams.id"), nullable=False)
    kickoff_time = Column(DateTime, nullable=True)
    home_score = Column(Integer, nullable=True)
    away_score = Column(Integer, nullable=True)
    status = Column(String, nullable=False, default="scheduled")

    gameweek = relationship("Gameweek", back_populates="fixtures")
    home_team = relationship("Team", foreign_keys=[home_team_id])
    away_team = relationship("Team", foreign_keys=[away_team_id])

Why foreign_keys=[...] is required, not optional, on home_team and away_team

Fixture has two foreign keys pointing at the same table, teams.id — home_team_id and away_team_id. Without telling SQLAlchemy explicitly which column each relationship should join on, it can't work out on its own which of the two foreign keys home_team is even supposed to follow — the same ambiguity a human reader would have looking at "two arrows into the same table" with no labels. foreign_keys=[home_team_id] and foreign_keys=[away_team_id] resolve that ambiguity directly. A single self-referencing foreign key (like SeasonTeam.team_id above) never needs this, since there's only one possible column for SQLAlchemy to pick.

Nullable Scores: A Fixture Exists Before It's Played

home_score and away_score are both nullable=True, 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

CheckConstraint("home_team_id != away_team_id") stops a team from playing itself — that's a genuine per-row check the database can enforce directly. 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 plain column-level constraint can't express that. 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 foreign key column on Fixture, 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.

The Pydantic Layer: Request & Response Shape

Following the same two-layer split this site's own fastapi-food-tracker-1 course already established — SQLAlchemy models describe what's actually stored, Pydantic schemas describe what a request or response is allowed to look like. Two representative examples here; the rest get built alongside their own routes in later chapters.

# schemas.py
from pydantic import BaseModel
from datetime import datetime
from typing import Optional


class TeamCreate(BaseModel):
    name: str
    short_name: str


class TeamResponse(BaseModel):
    id: int
    name: str
    short_name: str

    class Config:
        from_attributes = True


class FixtureResponse(BaseModel):
    id: int
    gameweek_id: int
    home_team_id: int
    away_team_id: int
    kickoff_time: Optional[datetime]
    home_score: Optional[int]
    away_score: Optional[int]
    status: str

    class Config:
        from_attributes = True

FixtureResponse's own home_score and away_score being Optional[int] reads directly off the nullable columns above — a fixture that hasn't been played yet serializes with both fields simply absent, no separate "played: false" flag needed to know that.

Connecting to PostgreSQL & Creating the Tables

# database.py
import os
from dotenv import load_dotenv
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base

load_dotenv()

DATABASE_URL = os.getenv(
    "DATABASE_URL", "postgresql://postgres:@localhost:5432/pl_predictor"
)

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

A single line in main.py, after importing models, creates every table above against the real database from Chapter 1:

import models
from database import engine

models.Base.metadata.create_all(bind=engine)

Where This Course Is Headed

Managing the 20 competing teams each season — real CRUD against SeasonTeam (Chapter 3); the fast click-to-pair fixture-entry UI that creates Fixture 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 SeasonTeam 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 SeasonTeam table rather than a boolean column on Team itself, and name the two later chapters that operate directly on SeasonTeam rows.

📄 View solution

Exercise 2

Explain why Fixture's home_team and away_team relationships both need an explicit foreign_keys=[...] argument, while SeasonTeam's team relationship doesn't need one at all.

📄 View solution

Exercise 3

Explain why the "same team playing twice in one gameweek" rule is enforced in the fixture-entry UI rather than as a database constraint, and contrast that with the "team can't play itself" rule, which is enforced directly by CheckConstraint.

📄 View solution

Chapter 2 Quick Reference

Team — every team ever tracked, permanent regardless of promotion/relegation
Season — one real Premier League season, with an is_current flag
SeasonTeam — the join table recording which 20 teams compete in a given season; Chapters 3 and 9 both operate directly on it
Gameweek — 1 through 38 per season, unique per (season_id, number)
Fixture — home_team_id/away_team_id both self-reference teams.id, needing explicit foreign_keys=[...] on each relationship
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" (CheckConstraint)
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