Say there's a sport that can either be contested between two individuals, or two teams comprised of several individuals. So obviously we need the standard players, teams and teams_players tables. But how do we design the related tables like matches, competition_entries, etc. (that need to reference either a player or a team) to accommodate this? I've come up two solutions, both have their drawbacks...

I could make a more generic competitors(id, type, player_id, team_id) table, where "type" is either player or team, and the last two columns reference the obvious tables, with precisely one of player_id and team_id being null. Then anything that needs to reference either a player or a team will reference the competitors table instead (e.g. in the matches table there'd be competitor1_id and competitor2_id columns). However, to my knowledge it would be impossible (within MySQL at least) to have appropriate constraints on this table - namely, that either one of player_id and team_id must be null, while the other one must be set.

The alternative which seems like a more "pure" solution but in practice is extremely long-winded, is just to have everything separate. Have a team_matches and an individual_matches table, have a team_competitions and an individual_competitions table, a team_scores and an individual_scores, etc... These tables will have mostly identical designs, except one will have a player_id column and the other team_id.



Here is a possible solution:

teams(team_id primary key, foreign key for competitors, other attributes)
players(player_id primary key, foreign key for competitors, other attributes)
matches(match_id primary key, first_comp_id foreign key for competitors, second_comp_id foreign key for competitors, other attributes)


teams_players(team_id foreign key for teams, player_id foreign key for players) primary key(team_id, player_id)

if the relation is many-to-many or simply an attribute team_id into player if the relation is one-to-many.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.