Laravel Eloquent relationships in Sports

Question

I'm learning Laravel 8.x and I decided to do a website project for our community where we have different sport events for our youth throughout the year. Football, volleyball, basketball, etc. While I think I managed to get pretty far, I have problems understanding how I should design the matches table.

Here is a partial diagram of my current database, which I will explain in more detail:

Each sport is played in form of tournaments throughout the year. Each tournament can have 1 or more groups and 1 or more playoffs. Depending on teams available we create 1-4 groups and usually to let all kids play longer, but still keep it competitive, we could form 2 different playoffs for each tournament, depending on group positions.

I created Models:

Sport

public function tournaments() {
    return $this->hasMany('App\Models\Tournament');
}

Team

public function tournaments() {
    return $this->belongsToMany('App\Models\Tournament')->withTimestamps();
}

public function groups() {
    return $this->belongsToMany('App\Models\Group')->withTimestamps();
}

public function playoffs() {
    return $this->belongsToMany('App\Models\Playoff')->withTimestamps();
}

Group

public function tournaments() {
  return $this->belongsToMany('App\Models\Tournament')->withTimestamps();
}

public function teams() {
  return $this->belongsToMany('App\Models\Team')->withTimestamps()->withPivot(
    'position',
    'points',
    'wins',
    'losses',
    'draws',
    'off',
    'def'
  );
}

Playoff

Similar to Groups

Tournament

public function sport() {
    return $this->belongsTo('App\Models\Sport');
}

public function groups(){
    return $this->belongsToMany('App\Models\Group')->withTimestamps();
}

public function playoffs(){
    return $this->belongsToMany('App\Models\Playoff')->withTimestamps();
}

public function teams() {
    return $this->belongsToMany('App\Models\Team')->withTimestamps();
}

As groups / playoffs belong to tournaments, I decided to use the group_team / playoff_team as a "team signup to a tournament", instead of creating another table with tournament entries and tournament standings.

What I have problem now is creating the matches table. How should I do it?

Real example would be: We have a tournament in Basketball. Team A and Team B are both in Group A. They play a match and Team A wins 21:19. Since we are in group stage still, the group_team table should get updated.

With already doing all these many-to-many relationships until this point, I am not sure how to design a match table, which should probably have:

team1_id
team2_id
team1_score
team2_score
group_id (?)

Thank you for suggestions.

user_1480588 · Answer

I ended up creating Match model:

public function team1() {
    return $this->belongsTo(Team::class, 'team1_id');
}

public function team2() {
    return $this->belongsTo(Team::class, 'team2_id');
}

public function group() {
    return $this->belongsTo(Group::class,);
}

public function playoff() {
    return $this->belongsTo(Playoff::class,);
}

with

$table->foreign('group_id')
            ->references('id')->on('groups')
            ->onDelete('cascade');

$table->foreign('playoff_id')
            ->references('id')->on('playoffs')
            ->onDelete('cascade');
        
$table->foreign('team1_id')
            ->references('id')->on('teams')
            ->onDelete('cascade');

$table->foreign('team2_id')
            ->references('id')->on('teams')
            ->onDelete('cascade');

Maybe not the best approach and something I will need to revisit down the line, but it works.

Laravel Eloquent relationships in Sports

1 Answers