The League With Too Many Loyalties

A hard Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
hard
Seniority
L5
Asked in
Flipkart

Problem

Design a data model for a sports tournament platform. The platform tracks multiple leagues, each with multiple teams. Players belong to teams, but can also represent national teams in separate competitions. Each match has two teams, takes place at a stadium, and produces per-player and per-team stats. Analytics need cumulative player scores across all matches in a tournament.

A player can belong to many teams. The schema must agree.

Worked solution and explanation

What this really is

This is a time-bounded many-to-many wearing a sports jersey. Anyone can draw players and teams. What separates candidates is where the team lives. Put a team_id on players and the model knows only who a player plays for today. Every past goal silently moves to the new club the moment a transfer is recorded, and a striker who also plays for his country collapses into one team.

Building it

Step 1: Declare the grain before any column

fact_player_match_stats is one row per player per match. The natural key is the composite (match_id, player_id); a surrogate player_match_stat_id is the PK and the pair gets a unique constraint. goals, assists and minutes are fully additive, so a tournament total is a plain SUM.

Step 2: Make player-to-team an effective-dated junction

player_team_memberships holds player_id, team_id and a validity window. A club spell and a national-team spell are two rows that may overlap in time, which is exactly what a single FK cannot express. An open spell has end_date as NULL.

Step 3: Give matches two team FKs, not a junction

A match has exactly two teams in fixed roles. home_team_id and away_team_id both reference teams, and home_score / away_score carry the team-level result on the match row itself. A bridge here buys nothing and turns every fixture query into a self-join.

Step 4: Separate the competition from the club structure

teams hang off leagues; matches hang off tournaments. A league season is a tournament with a league_id; an international cup has league_id as NULL and its teams carry team_type of 'national'. That is how one player appears in both competitions cleanly.

Grain of the fact is (`match_id`, `player_id`); `team_id` on the fact freezes the attribution at load time
  • leagues: league_id BIGINT PK, name VARCHAR, country VARCHAR
  • tournaments: tournament_id BIGINT PK, league_id BIGINT FK, name VARCHAR, season VARCHAR, start_date DATE, end_date DATE
  • teams: team_id BIGINT PK, league_id BIGINT FK, name VARCHAR, team_type VARCHAR
  • stadiums: stadium_id BIGINT PK, name VARCHAR, city VARCHAR, capacity INT
  • players: player_id BIGINT PK, full_name VARCHAR, dob DATE, nationality VARCHAR
  • player_team_memberships: membership_id BIGINT PK, player_id BIGINT FK, team_id BIGINT FK, start_date DATE, end_date DATE
  • matches: match_id BIGINT PK, tournament_id BIGINT FK, home_team_id BIGINT FK, away_team_id BIGINT FK, stadium_id BIGINT FK, kickoff_ts TIMESTAMP, home_score INT, away_score INT
  • fact_player_match_stats: player_match_stat_id BIGINT PK, match_id BIGINT FK, player_id BIGINT FK, team_id BIGINT FK, goals INT, assists INT, minutes INT
  • tournaments.league_id references leagues.league_id
  • teams.league_id references leagues.league_id
  • player_team_memberships.player_id references players.player_id
  • player_team_memberships.team_id references teams.team_id
  • matches.tournament_id references tournaments.tournament_id
  • matches.home_team_id references teams.team_id
  • matches.away_team_id references teams.team_id
  • matches.stadium_id references stadiums.stadium_id
  • fact_player_match_stats.match_id references matches.match_id
  • fact_player_match_stats.player_id references players.player_id
  • fact_player_match_stats.team_id references teams.team_id
SCD Type 2 on players
Versions players with a current team_id. Works while a player has one team at a time. The moment a club spell and a national call-up overlap, you need two current rows for one person and the dimension stops meaning anything.
Effective-dated junction
player_team_memberships is the SCD2 idea moved onto the relationship itself. Concurrent spells are just two rows; players stays a stable, one-row-per-person dimension.
Tournament scoring leaders, transfer-correct
SELECT
    p.full_name,
    t.name AS tournament,
    SUM(s.goals) AS goals,
    SUM(s.assists) AS assists
FROM fact_player_match_stats s
JOIN players p ON p.player_id = s.player_id
JOIN matches m ON m.match_id = s.match_id
JOIN tournaments t ON t.tournament_id = m.tournament_id
JOIN player_team_memberships ptm
  ON ptm.player_id = s.player_id
 AND ptm.team_id = s.team_id
 AND m.kickoff_ts >= ptm.start_date
 AND (ptm.end_date IS NULL OR m.kickoff_ts < ptm.end_date)
WHERE t.tournament_id = 7
GROUP BY p.full_name, t.name

The membership join is a guard: a stat row whose team_id had no active spell at kickoff drops out

Common follow-up questions