Help with rating system

[jbachris]

I have this forumla for rating soccer teams in our local league:

The ratings are based on the following formulas:

New Rating = (weighted wins + 1) / (games played + 2)

Weighted Win = Opponent Rating + (0.5 * K)

Where K is the game outcome. 1 for a win, 0 for a draw, and -1 for a loss.

I have a few tables:

TEAMS
team_id
team_name
team_region

GAMES
game_id
game_date
game_away_team
game_away_score
game_home_team
game_home_score

RATING
team_id
newest_rating
previous_rating

The game_away_team and game_home_team are foreign keys with the team_id.

My question is what is the best way to compute the newest rating?

[kakki]

i think its best to have the db handle all the basic stuff, like finding values and computing something maybe a single insert ( with the temp_tables ) will do the job....fast
something like

-- create temp-table for calculation
drop table if exists temp_weighted_teams;
create tabelle temp_weighted_teams as
      select home.team_id as homeTeamID,home_rating.newest_rating as homeRating,games.game_away_score as homeGoals,
             away.team_id as awayTeamID,away_rating.newest_rating as awayRating,games.game_away_score as awayGoals
      from TEAMS as home, RATING as home_rating, GAMES as games, TEAMS as away,RATING AS away_rating
      where home.team_id=home_rating.team_id
          and away.team_id=away_ratig.team_id
          and games. game_away_team=away.team_id
          and games.game_home_team=home.team_id
          and game.date>=(select DISTINCT(date) from GAMES order by date limit 0,1 ); -- for the last gameday,limit 0,3 for the last weekend

drop table if exists temp_weighted_result;
-- home win
create table temp_weighted_result as
       select homeTeamID as team_id, awayRating+0.5 as newValue
       from temp_weighted_teams
       where homeGoals>awayGoals;
-- away win
insert into temp_weighted_result
       select awayTeamID as team_id, homeRating+0.5 as newValue
       from temp_weighted_teams
       where awayGoals>homeGoals;
-- equals
insert into temp_weighted_result
select awayTeamID as team_id, homeRating as newValue
       from temp_weighted_teams
       where awayGoals=homeGoals;
insert into temp_weighted_result
       select homeTeamID as team_id, awayRating as newValue
       from temp_weighted_teams
       where homeGoals=awayGoals;
--home loose
insert into temp_weighted_result
       select homeTeamID as team_id, awayRating-0.5 as newValue
       from temp_weighted_teams
       where homeGoals<awayGoals;
-- away loose
insert into temp_weighted_result
       select awayTeamID as team_id, homeRating-0.5 as newValue
       from temp_weighted_teams
       where awayGoals<homeGoals;
drop table if exists temp_rating;
create table temp_rating as
       select RATING.team_id as team_id, RATING.newest_rating as previous_rating,temp_weighted_result.newValue as newest_result
       from temp_weighted_result,RATING
       where temp_weighted_result.teamID=RATING.team_id;
delete from RATING;
insert into RATING
       select * from temp_rating;

advantages for that design:
if you do it that way, you can use different mechanisms than normal php
( you can cron_job this with the bash, use java/asp/... for that )