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
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.
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
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.
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.
9 People are following this question.
