What’s the best way to model similar tables with overlapping functionality?


I’m building a sports event/tournament/league management app for myself, and I’m stuck on the best way to model how Teams should relate to the different competition types.

Some background regarding the goals of the model:

  • using postgresql & graphql
  • Players earn points for participating in various events (tournaments &/or leagues) – calculated after each event.
  • Teams are made up of 1 to multiple players (depending on type of tournament or league) through a team_players join table.
  • Teams currently just have a tournament_id to join them to a tournament.
  • I’m making a separate Leagues table due to enough differences from how Tournament data is stored.
  • However, Tournament Teams & League Teams are exactly the same other than their event type & a couple quirks with how the points system calculates.

The crux of the question: Should I split out LeagueTeams to their own table & duplicate all the application logic that overlaps with TournamentTeams, or should I use one Teams table with 2 foreign keys (league_id or tournament_id)?

I feel like using one Teams table would be faster to get everything wired up on the application side initially, but I’m concerned it could end up complicating things long term…? Will it be more irritating always having to remember to query Teams by their event type in the future? Just trying to make sure I’m not missing something. Any guidance?