Laravel Eloquent relationships in Sports

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('AppModelsTournament');
}

Team

public function tournaments() {
    return $this->belongsToMany('AppModelsTournament')->withTimestamps();
}

public function groups() {
    return $this->belongsToMany('AppModelsGroup')->withTimestamps();
}

public function playoffs() {
    return $this->belongsToMany('AppModelsPlayoff')->withTimestamps();
}

Group

public function tournaments() {
  return $this->belongsToMany('AppModelsTournament')->withTimestamps();
}

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

Playoff

Similar to Groups

Tournament

public function sport() {
    return $this->belongsTo('AppModelsSport');
}

public function groups(){
    return $this->belongsToMany('AppModelsGroup')->withTimestamps();
}

public function playoffs(){
    return $this->belongsToMany('AppModelsPlayoff')->withTimestamps();
}

public function teams() {
    return $this->belongsToMany('AppModelsTeam')->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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0