Lets say we need to update our values in db every second for game like scenarios, where resources change at some rate of time. Ex: 5 gold/sec I thought of storing timestamps and calculating on demand. But then scenarios like warehouse can hold only x amount scenario, Other x amount increase or decrease at any point of time scenarios. Including cases where the rate changes at different points of time as well.
How do we handle such scenarios in the backend?
Updating a database every second is inefficient, so using timestamps and calculating on demand is a good idea.
If a warehouse has a storage limit, then when you calculate that a warehouse should now have 5000 gold, but it only has space for 1000, you only set it to 1000.
If someone sends you gold (increases at a random time), you first calculate what the warehouse should be at before you add the new resources.
If someone steals gold from you (decrease at a random time), you do the same thing.
Essentially, whenever the amount of gold is accessed, you update it to be current to the current time based on the last time it was updated.
For example:
Example where rate changes (like if your production was upgraded while offline):
You will know the last timestamp and current time
If any pending events don't change the rate, you can treat the whole inactive time as the same rate
If the rate did change, you need to know at what time(s) did the rate change. Then you have many segments of differing rates: If the example starts at 1:00, then at 3:00 you know the production was changed, so you can have 2 segments: 1:00 ~ 3:00, 3:00 ~ 6:00 (you split the inactive time segment by the time(s) the rates change). You assign a rate to each segment, and then you can calculate the gold increase in each segment separately and sum them together. Again, you do this whenever the gold value is accessed.