x

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

fashraf gravatar image

fashraf
418 12 14 20

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

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x238
x107

asked: Aug 31, 2012 at 06:54 PM

Seen: 1013 times

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