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 ·
Show more comments

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.