ELO rating - mysql design
I am a completely new starter in programming. I just created a system for personal use. It is possible to add the results of sports matches of two players.
Everything works fine so far. Now I'm going to add the ELO rating system.
I cannot figure out how should I create my database.
The problem is that I cannot figure out how to keep the correct rating score record in case any game result is deleted at any time. For example, someone adds erroneous data and the game record should be deleted.
So how should the database project be set up, what if I remove any game from the middle of the entries, the rating is recalculated correctly?
illustrative example of my problem, I cannot solve (rating calculation is just informative):
Player1 (rating 1200), Player2 (1200)
Game1 played, player1 wins.
Rating after game: P1:1231, P2:1169
Game2 played, Player1 wins.
Rating: P1:1259, P2:1141
Game3, Player2 wins
Rating: P1:1220, P2:1180
So then I realize that there is false data in game 2, I want to delete it.
What's going to happen? I delete game2, then the script has to change the rating, like grab the rating of both players from one game before, after game1. which would be P1: 1231, P2: 1169 And then recalculate the entire rating after the game is deleted. So Game3, the winner of the player, the rating should be P1: 1198, P2: 1202
I can't just delete game2 and the difference rating ... the rating has to be recalculated from the previous game and for everyone after the game.
It is more difficult if there are 3 or more players and they played games after what I want to delete. Because of their ratings have changed. And if the game is deleted, then all the rating changes of the games that were played afterwards must be recalculated one by one.
I hope my problem is clear ...
So the question is, could you please give me, a complete php / .mysql starter, hints of what the database logic and rating calculation process should be for my situation.
Thanks in advance.
source to share
Basically, there will be two tables: players(name,whatever else,rating)
and games(player_id_white,player_id_black,result=1,0,-1,date)
.
After the game has been deleted, reset all players' ratings back to their original value and recalculate them from zero. Just read the game table sorted by date, update the scores in memory, and finally write them back to the players table.
This is kind of a straightforward and crude solution, but should work for you until (until) you have thousands of players and millions of games.
source to share