Laravel赛事网站数据库设计：关联关系构建与方案优化咨询

Hey there! Let's break this down step by step since you're new to Laravel and database design for esports/event sites. I'll walk you through a clean, scalable table structure, Laravel model relationships, and optimizations to match your exact needs.

1. Database Table Design #

We'll use 4 core tables to avoid redundancy and keep relationships clear:

teams (Stores basic team info) #

maps (Stores map metadata) #

matches (Stores core match info) #

Note: We'll calculate total scores dynamically instead of storing them directly (more on this later) to avoid data inconsistencies.

match_maps (Stores per-map scores for each match) #

This is the junction table that links matches to their individual map results:

2. Laravel Model Relationships #

Now let's define the relationships in your Eloquent models to make querying data seamless:

App\Models\Match.php #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Match extends Model
{
    // Define match status constants for clarity
    const STATUS_UPCOMING = 0;
    const STATUS_ONGOING = 1;
    const STATUS_COMPLETED = 2;

    protected $fillable = [
        'left_team_id',
        'right_team_id',
        'start_time',
        'status'
    ];

    // Relationship to left team
    public function leftTeam(): BelongsTo
    {
        return $this->belongsTo(Team::class, 'left_team_id');
    }

    // Relationship to right team
    public function rightTeam(): BelongsTo
    {
        return $this->belongsTo(Team::class, 'right_team_id');
    }

    // Relationship to all map results for this match
    public function matchMaps(): HasMany
    {
        return $this->hasMany(MatchMap::class)->orderBy('map_order');
    }

    // Dynamic attribute to calculate left team's total score
    public function getLeftTotalScoreAttribute(): int
    {
        return $this->matchMaps()->sum('left_team_score');
    }

    // Dynamic attribute to calculate right team's total score
    public function getRightTotalScoreAttribute(): int
    {
        return $this->matchMaps()->sum('right_team_score');
    }
}

App\Models\MatchMap.php #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class MatchMap extends Model
{
    protected $fillable = [
        'match_id',
        'map_id',
        'left_team_score',
        'right_team_score',
        'map_order'
    ];

    public function match(): BelongsTo
    {
        return $this->belongsTo(Match::class);
    }

    public function map(): BelongsTo
    {
        return $this->belongsTo(Map::class);
    }
}

App\Models\Team.php #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Team extends Model
{
    protected $fillable = [
        'name',
        'slug',
        'logo_url'
    ];

    // Matches where this team is the left participant
    public function matchesAsLeftTeam(): HasMany
    {
        return $this->hasMany(Match::class, 'left_team_id');
    }

    // Matches where this team is the right participant
    public function matchesAsRightTeam(): HasMany
    {
        return $this->hasMany(Match::class, 'right_team_id');
    }
}

3. Key Optimizations & Best Practices #

• Avoid redundant total scores: By using dynamic attributes (left_total_score/right_total_score), we ensure total scores always match the sum of map scores—no more manual updates or data mismatches.
• Index for performance: Add database indexes to foreign keys (left_team_id, right_team_id, match_id, map_id) and map_order to speed up queries.
• Preload relationships: When fetching a match, use eager loading to avoid N+1 database queries:

  $match = Match::with(['leftTeam', 'rightTeam', 'matchMaps.map'])->find($matchId);
  

• Status management: Use the defined constants in the Match model to keep status values consistent across your codebase.
• Blade view example: Display match data cleanly in your frontend:

  <div class="match-card">
      <h2>{{ $match->leftTeam->name }} vs {{ $match->rightTeam->name }}</h2>
      <div class="total-score">
          {{ $match->left_total_score }} - {{ $match->right_total_score }}
      </div>
  
      <h3>Map Results</h3>
      @foreach($match->matchMaps as $mapResult)
          <div class="map-result">
              {{ $match->leftTeam->name }} {{ $mapResult->left_team_score }} 
              <span class="map-name">{{ $mapResult->map->name }}</span> 
              {{ $mapResult->right_team_score }} {{ $match->rightTeam->name }}
          </div>
      @endforeach
  </div>
  

内容的提问来源于stack exchange，提问作者Philipp Kishkovarov