Elo rating system - using VB to update cells

[zeugma]

I am programming a database to automatically calculate a player's Elo rating (http://en.wikipedia.org/wiki/Elo_rating_system) for a foosball league. A user completes a form that asks who the two players were and which of them won. The change in each user's rating is calculated by a formula that uses both of their current ratings:

ExpectedA = 1/(1+10^((RatingA-RatingB)/400))
RatingDiff = 20(W-ExpectedA)
RatingNewA = RatingA+RatingDiff
RatingNewB = RatingB-RatingDiff
W is 1 if A wins, 0 if B wins.

Currently, my database has tables:
[Players] with fields PlayerID, FirstName, LastName, Rating
[Games] with fields GameID, PlayerGold, PlayerBlack, Winner

There are two sides to the foosball table, Gold team and Black team; the Winner field is allowed to contain either 'Black' or 'Gold'.


PlayerBlack and PlayerGold are the PlayerIDs of the two competing players.

How would the code look that, upon submitting a form that contains the two players names and which of them won, selects the Rating value for each player, performs the calculation, and then updates the Rating value for each player with their new rating?

[jzwp11]

Welcome to the forum!

In databases, if a value can be calculated, then it is generally not stored in the table but calculated on the fly when needed in queries, forms and reports. In your case, the rating is a calculated value, but then again so is the rating difference and the ExpectedA for that matter. Of course, we would need the initial rating of each player; that should be stored along with who wins and loses each game. From there, the rating can be calculated. The problem is that I am not quite sure how to handle the determination of ExpectedA since it depends on the ratings of the 2 players at the start of each game, and determining the rating of each player and their respective opponent for each game previously played will become quite calculation intensive if there are a large number of players and many games played. A compromise might be in order here. Perhaps, the expectedA and the rating difference values could be calculated at the end of a game and the rating difference values for each player stored. Using this approach, the current rating can be determining at any time by summing the rating differences for a player for each previous game played and adding that to their initial rating.

No matter what approach, I think we would need a date/time field for the game.

Players
-PlayerID primary key, autonumber
-FirstName
-LastName
-InitialRating

Games
-GameID primary key, autonumber
-dteGame (date/time field)

GamePlayers (this table will contain 2 records for each gameID since you have 2 players/game)
-GamePlayerID primary key, autonumber
-GameID foreign key to Games table
-PlayerID foreign key to Players Table
-Eval long number or integer field to hold 0 for loser or 1 for winner or you could just use a yes/no field, a checked (yes) would be the winner.

If we were to go with the compromise I suggested, then the rating difference field would go in the GamePlayers table above.

I think it would be great to get the opinion of others on the forum whether any calculated value should be stored in this particular situation.