I've been interested in developing a browser game ala OGame or Ikariam etc. Basically, a menu is displayed with your resources and, overtime, they increase depending on how advanced are you in the game.
Now then, the "resources" information for all players is stored in a MySQL database.
I've considered two methods to keep track of a player's resources:
Update database every second
Every second, something like
UPDATE `Players` SET `Gold` = `Gold` + `GoldProduction`
This is one option. However, performing a query every second for a potentially large table of players is not exactly appealing - but I might be underestimating my server. Am I?
Calculate it when needed
Since you get gold every second, technically the amount of gold any player has should be
(currentTime - creationTime) * goldProduction
What if the player spends gold? Well, gold spending should affect a variable
goldModifications + ((currentTime - creationTime) * goldProduction)
So if you spend 100 gold,
goldModifications would be
But wait. Players can upgrade their gold production. Hence,
goldProduction isn't constant and this formula is flawed...
... Maybe when you upgrade your gold production,
goldModifications will be modified by your current amount of gold (so if you had 1500, we would do
goldModifications += 1500), then
creationTime will be overwritten to the current time.
So I have two methods. One is incredibly simple (just update the database), but I am unsure if it is too much overhead or not. The other is very complex as there could be any number of factors affecting gold production, but certainly requires less processing power.
What should I do? What do those popular games actually do?