飞镖赛事统计数据库设计求助：选手单局数据关联问题

Hey Patrick, let's work through this database design problem together—tracking per-leg stats for dart players is totally manageable once we map out the core entities and their relationships clearly. Here's a structured approach that'll let you pull the exact data you need:

Core Entities & Table Structures #

Let's start with the foundational tables, each focused on a single responsibility:

1. players Table #

Stores basic info about every registered player:

• player_id (INT, PRIMARY KEY): Unique identifier for each player
• full_name (VARCHAR): Player's name
• nickname (VARCHAR, optional): For fun, if you want to track aliases
• created_at (DATETIME): When the player was added to the system

2. matches Table #

Tracks high-level details about each dart match:

• match_id (INT, PRIMARY KEY): Unique match ID
• match_date (DATETIME): Date/time the match was held
• venue (VARCHAR): Where the match took place
• match_format (VARCHAR): e.g., "501 best of 5 legs", "Cricket" (helps with context for stats)

3. match_players Table (Optional but Recommended) #

Links players to the matches they participated in—this ensures you only track stats for actual participants, avoiding invalid data:

• match_player_id (INT, PRIMARY KEY)
• match_id (INT, FOREIGN KEY → matches.match_id): The match in question
• player_id (INT, FOREIGN KEY → players.player_id): The participating player
• player_side (VARCHAR): e.g., "Player 1", "Player 2", "Team A" (to distinguish opponents)

4. legs Table #

Represents individual legs within a match:

• leg_id (INT, PRIMARY KEY): Unique leg ID
• match_id (INT, FOREIGN KEY → matches.match_id): The parent match
• leg_number (INT): Sequential number for the leg (1, 2, 3... to avoid duplicates per match)
• winning_player_id (INT, FOREIGN KEY → players.player_id, optional): Who won this leg

5. player_leg_stats Table (The Key to Your Problem!) #

This is where you store all per-player, per-leg statistics—this table bridges players and legs, and holds the specific data you need:

• player_leg_stats_id (INT, PRIMARY KEY)
• leg_id (INT, FOREIGN KEY → legs.leg_id): The leg these stats belong to
• player_id (INT, FOREIGN KEY → players.player_id): The player the stats are for
• total_dart_throws (INT): Total darts thrown in the leg
• score_start (INT): Starting score (e.g., 501 for standard matches)
• score_end (INT): Final score when the leg ended (0 for the winner)
• checkout_score (INT, optional): The score the player checked out on
• checkout_type (VARCHAR, optional): e.g., "Double 16", "Triple 20 + Single 1"

Relationship Breakdown #

• A single match has many legs (1:N relationship: matches.match_id → legs.match_id)
• A single leg has many player_leg_stats entries (one for each player in the leg, 1:N: legs.leg_id → player_leg_stats.leg_id)
• A single player has many player_leg_stats entries (across all legs they've played, 1:N: players.player_id → player_leg_stats.player_id)
• The optional match_players table creates a 1:N link from matches to players, ensuring only registered participants have stats recorded for a match

Example Query to Fetch Per-Leg Stats #

Here's how you'd pull all player stats for a specific match, grouped by leg:

SELECT
    m.match_id,
    m.match_date,
    p.full_name AS player_name,
    l.leg_number,
    pls.total_dart_throws,
    pls.score_start,
    pls.score_end
FROM matches m
JOIN legs l ON m.match_id = l.match_id
JOIN player_leg_stats pls ON l.leg_id = pls.leg_id
JOIN players p ON pls.player_id = p.player_id
WHERE m.match_id = 456; -- Replace with your target match ID

This query will return a row for each player's performance in each leg of the match—exactly the data you need to analyze individual leg stats per player.

Quick Tips #

• Add unique constraints to prevent duplicate legs per match (e.g., UNIQUE(match_id, leg_number) on the legs table)
• If you're tracking team matches, you can adjust the match_players table to include a team_id and add a teams table—this structure scales easily
• Index foreign keys (like match_id, player_id, leg_id) to speed up your queries as your dataset grows

内容的提问来源于stack exchange，提问作者Patrick Rey