question

fashraf avatar image
fashraf asked

Home and Away match Table structure.

![alt text][1] --This is the schedule table ... ![alt text][2] --This is the Team table ...I am creating a table that stores the Home and away matches of teams ..Now what i am stuck at is the design that i should go with ..at the moment I have one single row having the home and away column..but then it becomes difficult for me to differentiate since to add the scores there should be a unique id So do you suggest i should have a separate table for away matches ? please suggest me a DB design ..Thanks ! [1]: /storage/temp/382-o1e.png [2]: /storage/temp/383-t2o.png
databasedatabase-design
o1e.png (11.6 KiB)
t2o.png (11.9 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Post the DDL of your table
0 Likes 0 ·

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I'd have one Teams table and one Matches table. In Matches, I'd have Home- and Away-team-columns with foreign key to Teams table.
8 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

In one match, you have a HomeTeamID and an AwayTeamID. If you examine MatchID=x, and wants to know the score from the match where the HomeTeam was the AwayTeam and vice versa, you'd do (something along the line): SELECT m2.* FROM Matches m INNER JOIN Matches m2 ON m.HomeTeamID = m2.AwayTeamID AND m.AwayTeamID = m2.HomeTeamID AND m.MatchID=x
2 Likes 2 ·
I am having the same structure but my question is that when I tryin to keep score of the home and away match ...then there is a problem since the I'd of the row is same for home and away team. ..
0 Likes 0 ·
Why is that a problem? The ID is the ID of the game. To find all games for a team, check for rows where the team is either the home team or the away team.
0 Likes 0 ·
How do I score the home match ...and then the away match ? Both of them have the same ID ..so how do I differentiate the home match frm the away ??
0 Likes 0 ·
I still don't get it. If you have a Match, with a HomeTeam, an AwayTeam and two goal-columns, then you'd score a few points to the winner and a no points to the loser, or score some for both teams if it's a draw. Since the home-game and the away-game is the same game, it wouldn't make sense to separate them into two games. But I agree more and more with @Blackhawk-17 - post your DDL for the table(s) and post how you have tried to solve the problem so far.
0 Likes 0 ·
No..it's not away team and home team issue ..that I have figured already..it's the home and away matches ..like in football (soccer) you have a home match which is played in the home ground of the home team and then the other team is the away team ...then the same match is played after 3 months only this time the home team is the away team ..and the match is played in the oth teams home ground ..so I need to keep a score of these two matches ..
0 Likes 0 ·
But these two matches are two different matches and would be two rows in the database.
0 Likes 0 ·
yes ..they are two different matches but how do i relate them to each other ? for example if some one wants to know what was the score during the home match while reviewing the Away match ..how can both these matches relate to each other ?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.