Structure for Sports results

[simmi0505]

I am designing a database to track and report touch football game results.

It will have 1 table for team details with Team ID as the primary key.

Another table for Results which I think will have
Game ID, Team ID, Points for, Points against, etc

But this would require results entered twice for each game, once for each team.

Would like to have result reports and a form for entry of results the best format would be -

Game ID Team 1 Team 2 Points_Team1 Points_Team2

But if I use the above structure for the table how do you get a table with the results so you can calculate for each team no wins, draws, losses etc - given some will be team 1 and others team 2.

This would have been done a million times by others so dont want to reinvent the wheel.

Any advice would be appreciated.

 

[r937]

yes, it has been done lots of times, and yes, it is done with the structure you suggested --

GameID, HomeID, AwayID, HomeScore, AwayScore, ...

to show results for specific teams, you'd use a query like this --

select T.Name as TeamName
     , sum(
         iif(T.ID = G.HomeID
          , iif(G.HomeScore 
              > G.AwayScore,1,0)
          , iif(G.HomeScore 
              > G.AwayScore,0,1))
          ) as wins
  from Teams as T
inner
  join Games as G
    on T.TeamID in (G.HomeID,G.AwayID)
group
    by T.Name

r937.com | rudy.ca