league

[chiprivers]

I am working on a small script for which I need to be able to create and display a football league table (see my other post in misc forum for further information of project). 

 

I have three tables holding the data:

 

table: leagues

 

leagueID  (primary key)

league    (name of league)

 

table: teams

 

teamID    (primary key)

team      (name of team)

leagueID  (reference to table 'leagues')

 

table: fixtures

 

fixtureID  (fixtureID)

home        (reference to key of table 'teams')

away        (reference to key of table ' teams')

fixture      (date)

h_goals    (number of goals for home team once played)

a_goals    (number of goals for away team once played)

played      (value 0 before match, value 1 once played)

 

 

I am sure it is obvious, but the 'leagues' table stores the name of the different leagues, the 'teams' table stores the name of the different football teams and details which league they play in, and the 'fixtures' table details all of the games during the course of the season which is updated with the score once the game is played.

 

I now need to work out how to compile the data from the fixtures table into league tables.  The columns I need to display are:

 

team name, played, home win, home draw, home lose, away win, away draw, away lost, goal difference, points

 

I am thinking that the way I would approach this is to create another database table with columns matching the headers required above.  Then create a script that will pull the details from the 'fixtures' table and update the new database table.  I can then query this new table to display the final league table. 

 

Is this the best approach or is their a simpler way?