Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Nonesuch's avatar

Three Way Pivot Table - Best Practice?

I was wondering if this is the right approach to take when dealing with a sports scenario where throughout the year, there will be several leagues, and the team that the player is playing for is on a per league basis.

So for instance player 1 could be playing for team 5 in league 1, however, in league 2 the same player 1 might play for team 4 instead.

What is the best way to handle the relationships for this? My current thought process is to track this via a three-way pivot table called league_player and use belongsToMany to set up the relationship to handle showing all of the teams playing in that league and then all of the players playing in that team.

I seem to be getting duplicate relationship data when, for example, trying to get a list of teams playing in a league because each team has multiple players on it, which is making me question if I'm taking the right approach here or not.

Models/League.php

    public function teams()
    {
        return $this->belongsToMany(Team::class, 'league_player');
    }

Also, should my pivot table be called league_player_team instead?

Table structure

leagues
- id
- name

players
- id
- name

teams
- id
- name

league_player
- league_id
- player_id
- team_id
0 likes
4 replies
lbecket's avatar

A league has many teams, but a league does not belong to a team, so I would expect the relationship on your League model to look more like this:


public function teams()
{
	return $this->hasMany(LeaguePlayer::class);
}

With that said, you might consider multiple pivot tables:


player_teams
- player_id
- team_id

league_teams
- league_id
- team_id

The first approach allows you to pull back all player/team data for your league in a single relationship. This may be exactly what you want, in which case I wouldn't call that relationship "teams."

Using multiple pivots, on the other hand, just means more flexibility if you want to pull, say, only the teams without their associated players.

1 like
Nonesuch's avatar

Hello lbecket, thanks for your fast reply.

You reference a LeaguePlayer::class, does that mean I should have a model for my league_player pivot table? Is that standard practice to always have an additional model when dealing with pivot tables?

In your second example of using multiple pivot tables instead, how would I deal with players playing in different teams across the leagues, in general, the teams are gonna be static whereas the players will move around between teams depending on which league they are playing in, would you add a league_id column onto the player_teams pivot table in this case?

So for example team 2 might consist of player 1,5,6 in league 1 however in league 2, the same team 2 may have player 1,2,3 instead.

lbecket's avatar

@Nonesuch Yes, if you want to use an Eloquent relationship to the league_player table, then you would need to do so with a corresponding LeaguePlayer model.

If you go with the first approach of a single pivot and the relationship defined in my first post, you could get all teams and their players for a given league with a query like this:


$teams = League::with('teams')
	->where('id', 123)
	->get();

NOTE: the results will only include ID references to your team and its players. You'll have to isolate those IDs and run subsequent queries to get the necessary detail for the teams and players.

If you use multiple pivot tables to define the league/team and team/player relationships, then you may be able to define your relationships in such a way that you can get the necessary detail within a single query, but this whole matter is complicated by the fact that you're trying to treat teams as unique entities when in reality, each implementation of a team within a league is effectively a new entity.

Personally, I would take that approach where teams are instantiated within leagues. So, by that I mean Team 1 in League 1 gets a record with ID 123 and Team 1 in League 2 is effectively a different team entity with ID 124. Same name, but a different team as far as the data is concerned. This obviously introduces the need to somehow implement your teams within leagues, but I think it's a worthwhile trade when you consider how this simplifies your data model and that, for all intents and purposes, each league/team combo truly is a unique team with a unique mix of players.

Please or to participate in this conversation.