Denormalize to avoid many to many join table

Im working on a project which has these two tables:

leagues and teams

Each league can have many teams and a team can compete in many leagues.

Initially, I was going to create a join table league-teams; however, I don’t find it necessary to create a new join table. We are talking about 1500 football leagues, each with an average of 30 teams.

I am wondering from an optimization perspective to avoid another join table if it is a good practice to just have the two tables leagues and teams like this:

leagues   |   teams
id        |   id
name      |   name
country   |   league_id
...       |   external_id (this is an ID I get from a remote API)
          |   Some other fields ( team stats)
          |   ....

The extra fields collect some team stats from another table called seasons but I won’t go into the details to avoid adding complexity to the question.

So I was planning on creating a unique index for (league_id, external_id) This way I would avoid the extra join table league-teams, I will end up having one table of teams and each record with just a unique combination of external_id, league_id

I may end up with some duplicated data in the teams table but I don’t think that will be a big deal, An example of this would be:

id: 1
name: Arsenal FC
league_id: 1
external_id: 1620
------------------
id: 2
name: Arsenal FC
league_id: 2
external_id: 1620

Im not a database expert, so Im just looking for some opinions on what Im trying to do, I have heard about Denormalization and I wondering If this is a good way of doing it. Thanks

You need to sign in to view this answers