name: schema-design description: Design and document database schemas for BenchSight tables, relationships, and data models. Use when adding new tables or modifying existing schema. allowed-tools: Read, Write, Bash argument-hint: [table|relationship|migration]

Schema Design

Design database schemas for BenchSight.

Naming Conventions

Key Formatting

Format: {XX}{ID}{5D}

• No underscores in keys (except multi-entity)
• Zero-padded IDs
• Examples:
  • PL00123 (player)
  • GM00456 (game)
  • TM00007 (team)
  • PG_00123_00456 (player-game combination)

Core Tables (139 total)

Dimensions (50)

-- dim_players
CREATE TABLE dim_players (
    player_key VARCHAR(10) PRIMARY KEY,  -- PL{5D}
    player_id INTEGER NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    position VARCHAR(10),
    shoots VARCHAR(1),
    team_key VARCHAR(10) REFERENCES dim_teams(team_key),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- dim_teams
CREATE TABLE dim_teams (
    team_key VARCHAR(10) PRIMARY KEY,  -- TM{5D}
    team_id INTEGER NOT NULL,
    team_name VARCHAR(100),
    abbreviation VARCHAR(5),
    league_id UUID REFERENCES dim_leagues(league_id),
    created_at TIMESTAMP DEFAULT NOW()
);

-- dim_games
CREATE TABLE dim_games (
    game_key VARCHAR(10) PRIMARY KEY,  -- GM{5D}
    game_id INTEGER NOT NULL,
    game_date DATE,
    home_team_key VARCHAR(10) REFERENCES dim_teams(team_key),
    away_team_key VARCHAR(10) REFERENCES dim_teams(team_key),
    season_id INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

Facts (81)

-- fact_player_game
CREATE TABLE fact_player_game (
    player_game_key VARCHAR(20) PRIMARY KEY,  -- PG_{player_id}_{game_id}
    player_key VARCHAR(10) REFERENCES dim_players(player_key),
    game_key VARCHAR(10) REFERENCES dim_games(game_key),
    team_key VARCHAR(10) REFERENCES dim_teams(team_key),
    goals INTEGER DEFAULT 0,
    assists INTEGER DEFAULT 0,
    points INTEGER DEFAULT 0,
    shots INTEGER DEFAULT 0,
    toi_seconds INTEGER DEFAULT 0,
    -- ... many more columns
    created_at TIMESTAMP DEFAULT NOW()
);

-- fact_shots
CREATE TABLE fact_shots (
    shot_key VARCHAR(20) PRIMARY KEY,
    game_key VARCHAR(10) REFERENCES dim_games(game_key),
    shooter_key VARCHAR(10) REFERENCES dim_players(player_key),
    goalie_key VARCHAR(10) REFERENCES dim_players(player_key),
    period INTEGER,
    time_seconds INTEGER,
    x_coord DECIMAL(5,2),
    y_coord DECIMAL(5,2),
    shot_type VARCHAR(20),
    result VARCHAR(20),  -- Goal, Save, Miss, Block
    xg DECIMAL(4,3),
    created_at TIMESTAMP DEFAULT NOW()
);

QA Tables (8)

-- qa_validation_summary
CREATE TABLE qa_validation_summary (
    validation_key VARCHAR(20) PRIMARY KEY,
    run_timestamp TIMESTAMP,
    table_name VARCHAR(100),
    check_name VARCHAR(100),
    status VARCHAR(20),  -- PASS, FAIL, WARNING
    details JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

Relationships

dim_players ─┬─< fact_player_game >─┬─ dim_games
             │                       │
             └───< fact_shots >──────┘
                        │
                        └─── dim_teams

Indexes

-- Performance indexes
CREATE INDEX idx_player_game_player ON fact_player_game(player_key);
CREATE INDEX idx_player_game_game ON fact_player_game(game_key);
CREATE INDEX idx_shots_game ON fact_shots(game_key);
CREATE INDEX idx_shots_coords ON fact_shots(x_coord, y_coord);

Migration Template

-- migrations/YYYYMMDD_description.sql

-- Up
ALTER TABLE fact_player_game ADD COLUMN expected_goals DECIMAL(4,3);

-- Down (in separate file or comment)
-- ALTER TABLE fact_player_game DROP COLUMN expected_goals;

Output

Schema documentation goes to:

docs/data/schema/{table_name}.md