Database Design – Many-to-Many Cardinality Ratio Between Teams and Matches

database-designdatabase-diagramsmany-to-many

Diagram

I created the following diagram:

Diagram

Considerations

So I will create two tables, one called Match and the other one called Teams. In Match there will be data about two teams, Away and Home.

Let us take into account that each team can have take part in many matches, and a match can have exactly two teams.

Questions

  • Is this considered a many-to-many (M:N) relationship?
  • Is there anything I can do to change it?

Best Answer

What i want to know is if this is considered a many to many relationship.

No, it is not a Many-To-Many. Match has two separate relationships with Team - Home Team and Away Team. These relationships are One-To-Many - One Team can have many Matches as Home Team but one Match has only one Home Team. This repeats for Away Team but they are not the same relationship.

And if there is anything is can do to change it?

To what, and why?

Yes, you could make these relationships Many-To-Many, so a Match can have many Home Teams and many Away Teams, but that data model doesn't fit the use case in this scenario - it has been modelled correctly already.

You could also make these relationships One-To-One, meaning a Team can only have one Home Match and one Away Match total, but again this data model would not fit the use case.

Related Question