deebow's avatar

Two-way Many-To-Many Polymorphism: Table structure suggestion.

Hi guys, I need your suggestion or insights.

I have the following entities.

  • Game
  • Tournament
  • Club
  • Player

Clubs and Players may participate in many Games and Tournaments.

My two options:

  1. Two-way Polymorphic Many-to-Many:
    • eventables where both columns are polymorphic:
      • eventable: game, tournament
      • contenderable: club, player
  2. Separate pivot for each:
    • game_club, game_athlete, tournament_club, tournament_athlete

But at this point, I'm not really sure which is the best. I want to go with Option 1 but I don't see much discussions that relates to it.

Please let me know what's your take.

Thank you!

0 likes
8 replies
martinbean's avatar

@deebow Would a tournament not be made up of multiple games? Personally, I’d have a Game model, that then has many “participants”, which would be your polymorphic relation (a “participant” would either be a club or individual player).

For tournaments, I’d just have a has-many relation to games. Again, you could add a has-many polymorphic relation to participants if you wanted to show what clubs/players were playing in a tournament, but players in the constituent games had not been decided yet (e.g. matches still needed to drawn).

So you’d have the following tables:

  • clubs
  • players
  • games
  • tournaments
  • game_participants
  • tournament_participants

You could try and be “clever” and create some sort of “participants” table with two polymorphic relations linking a club/player to a game/tournament, but for the sake of clarity I’d just have a table each for games and tournaments, otherwise your queries and model relations may get a bit gnarly.

1 like
Glukinho's avatar

Can a club/player really participate in a tournament? Or in fact they can participate in a game which is a part of a tournament?

Can a player participate in a game/tournament by himself, without a club?

Maybe your real relations are:

Player -> belongsTo -> Club  
Club   -> hasMany   -> Player

Club -> belongsToMany -> Game  
Game -> belongsToMany -> Club

Game       -> belongsTo -> Tournament  
Tournament -> hasMany   -> Game

And Player is related to a Tournament/Game not directly but through several other relations.

2 likes
reaz's avatar

Do you mind sharing what sports is this relates to? In general knowledge, it seems like player participate in a game, which belongs to a tournament. Can player be part of a tournament with out being part of a game? Second layer is , can a player participate in a game without being part of a club? If they cant, then again players belong to club, club participates in game, which is belongs to a tournament.

deebow's avatar

Thanks everyone for your input :)

Since I have the following relationships:

Athlete -> BelongsTo -> Club
Club -> HasMany -> Athlete

Game -> BelongsTo -> Tournament
Tournament -> HasMany -> Game

I could go with contenderables, having 2 polymorphic columns for game/tournament and athelte/club, but it seems very complex, and I just want to simplify things, so I ended up with the following:

game_participants
tournament_participants
deebow's avatar

Although I'm having problems with making barryvdh/laravel-ide-helper work with this.

The problem is, I decided to make these pivot into a model extending Illuminate\Database\Eloquent\Relations\MorphPivot, since there are additional columns to each pivot, and it's much easier to manipulate it this way:

game_participants
	- game_id
	- participantable
	- more columns...

tournament_participants
	- tournament_id
	- participantable
	- more columns...

Now, since ide-helper expects the MorphPivot model to have a singular database migration, like game_participant and tournament_participant... it can't be analyzed, so it's not generating the necessary docblocks.

deebow's avatar

Ahh, silly... I could specify the table name in the model protected $table = 'game_participants' for example... then the ide-helper will then recognize it.

Please or to participate in this conversation.