I am building a browser php game.
There will be resources like metal, wood, food etc. Players will be getting the resources all the time (gaining resources speed depending on buildings/mines/farms levels).
The number of resources is saved in database resources table. Lets say now that someone will be getting 50 000 of metal hourly. What is the best way to save these values to database or recalculate them?
It would be crazy to add these values to the resources table every second to keep it updated. How to do it best?
If you can afford a stateful design, I have found that it is usually best to keep and maintain them in session, and aggregate the changes and write them out to the database at set intervals (of say 10 minutes), or when the session ends.
High rates of update can kill database performance: this impact is multiplied when the table you're writing to has any significant indexing. Different databases can support different transaction rates, and if you have more than a couple users, once-per-second updates will just kill performance.
An alternative is to write out these updates to a local or temporary queue table, containing only an index on the autoincrement field, and to have a sweeper process blow through it periodically to add those updates to the eventual target table at low priority. This keeps the update overhead lower, and reduces contention to the critical table, but it also means that your application logic will have to read the database value, and add the "pending" changes, before it receives a usable value.
A last alternative that is kind of the midpoint of the two above ones is to use a queue for storing pending database changes, but it would make it more difficult to calculate point-in-time values when there are unwritten changes still in the queue.