We are creating a RTS mobile game and we face some problem dealing with updating a lot of object in db too often.
Our game is a RTS in which players can attack enemies cities an take them. The world is procedural and potentially infinite, when players spawn we extend the map if needed (no city left). Our map is reset every month. Each city hold an army and produce an amount of army based on the city level. We also have some items that the player can equip and unequip. Those items may give some army production bonus.
At the biginning most of players had around 30-50 cities. But since the player number increased some players now have over 1000 cities.
Our data structure look like this (mongodb)
City: - id (ObjectId) - location [x(int), y(int)] - level (int) - armyAmount (double) - lastArmyUpdate (Date) - armyProductionMultiplier (double) Player: - id (ObjectId) - Name (string) - Items [key (int), equiped(bool)] - Cities [cityId (ObjectId)]
When a city is attacked we refresh the army based on the last update and the city production.
army = oldArmy + cityProduction*armyProductionMultiplier*TimeSinceLastUpdate
It works well as we only update the value when needed.
But when players equip an item that give them army production bonus we need to update the multiplier on each city the player own and to update the army amount considering the old multiplier before applying the new one. If the player only change his item once per day it's ok but if he swap every minutes it will make a lot of database query and waste a lot of cpu time.
We could set a timer between items swap but it would be frustrating for the player.
QUESTION: Is there a db pattern or a better way to reduce the cost of the item swapping.