Home and Away match Table structure.

alt text --This is the schedule table ... alt text --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 !

o1e.png (11.9 kB)
t2o.png (12.2 kB)
more ▼

asked Aug 31, 2012 at 06:54 PM in Default

avatar image

538 17 22 29

Post the DDL of your table

Aug 31, 2012 at 06:58 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Aug 31, 2012 at 07:29 PM

avatar image

Magnus Ahlkvist
22.5k 20 44 43

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. ..

Aug 31, 2012 at 09:04 PM fashraf

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.

Aug 31, 2012 at 09:54 PM Magnus Ahlkvist

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 ??

Aug 31, 2012 at 09:56 PM fashraf

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.

Aug 31, 2012 at 10:16 PM Magnus Ahlkvist

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
Sep 03, 2012 at 06:54 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 31, 2012 at 06:54 PM

Seen: 1548 times

Last Updated: Sep 03, 2012 at 06:54 AM

Copyright 2018 Redgate Software. Privacy Policy