CODEWITHSUNDEEP
mysqldatabasemany-to-manyentity-relationship
rj487
rj487

Reputation: 4634

How to design a relationship between team and game?

I have come up with two options to relate teams and games in my database; which one is most appropriate?

I was trying to make it simple, here is what I want. Teams have many games, and each game has a home team and an away team. It's a many to many relationship.

Here is what I thought:

enter image description here

I am not sure whether I should put a boolean field called ishome in competes table or add two fields called home_id and away_id, and set them as foreign key to references team.id like in following picture.

enter image description here

Upvotes: 2

Views: 857

Answers (1)

juergen d
juergen d

Reputation: 204854

If you have always 2 teams playing against each other you can use

competes table
--------------
home_team_id
away_team_id
game_id

like in your 2nd option. But if you have a dynamic number of teams in a game then use the other.

Upvotes: 1

Related Questions