I created the following diagram:



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.


  • Is this considered a many-to-many (M:N) relationship?
  • Is there anything I can do to change it?
  • Yes, it's a Many-to-Many relationship. What do you want to change it to and why? The design and model should fit the use-case, not the other way around. – Queue Mann May 1 at 17:56
  • 1
    Hmm, first thought was that you have two one to many relationships. Not one many to many relationship but you could model this with a many to many relationship junction table between MatchId and TeamId with an attribute for home or away. I don't suggest that though. Enforcing a match has exactly one home team and one away team and they are different is easier done with your current design. – Martin Smith May 1 at 18:14
  • Keep this design. Make home team and away team both mandatory. Add a check constraint that they must be different. – Colin 't Hart May 1 at 19:16

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.

  • Yeah ok so its wasnt actually a many to many realtion ship, and as for the second question i should have phrased it as what would i need to do to changes it if it was a many to many relationship. – Son Nguyen May 2 at 2:26

Is this your full design or just a trivial example?

Suppose you want to store information about a team in a match

  • who was captain of the team during the match
  • any kind of stats of the team during the match
  • and so ..

If this would be the case, I would opt for a many-to-many design.

  • A Many-To-Many relationship between Team and Match would not suit the use case regardless of the amount of data you want to capture about the match. The data model for this scenario still has to limit a single Match record to only one Home Team and one Away Team. These are separate relationships. You might have separate tables to store the additional data but that table would have a One-To-One relationship with Match. – HandyD May 2 at 0:22

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.