Best way to manage resources in a game

Question

Recently I have started game development and i am in process of make an MMORTS game (Massively Multiplayer Online Real Time Strategy) but i am stuck at one point and need suggestion how to manage it. Below is the case:-

General Game Information

All the data is saved in SQL Server 2008
This game is being developed to be used in mobiles.

above is the game resources icon with its value.

Every resource has its per hour increase suppose:-

Wood :- 100/hr
Stone :- 100/hr
Food :- 100/hr

Problem

The game i am developing would have 10k users using this game at the same time that means i cannot continuously update the resource value. So my question is, how do i manage the resources of the game (wood, stone, food)?

Why update resource value in database?

It is because if a person attacks a city then how much resources (wood, stone, food) are available to be captured.

My Current Approach

Changing value at the time when the city is attacked but is there any better method of doing it?

Please feel free to ask any questions and to change the tags of the question as i am unaware of what tag would fit here.

Answer 1

Why do you need to update it at all, if you have a starting value and a rate of increase, it is a fairly simple calculation. StartValue + (HoursSinceStart * Rate), this will cover your displays. Then each time you add/remove a resource by a specific action, insert a new record with the post action amount, and a new time stamp.

For example, using Wood, User1 starts with 100:

Resource

ResourceID  Name    HourlyIncrease
------------------------------------
1           Wood    100
2           Stone   100
3           Food    100

UserResource

UserID  ResourceID  Value   CreatedDateTime
---------------------------------------------
1       1           100     2015-09-04 10:00:00

To get the value at @DateTime, you can use:

DECLARE @DateTime DATETIME2 = SYSDATETIME(),
        @UserID INT = 1,
        @ResourceID INT = 1;

SELECT  TOP 1
        Quantity = Value + (r.HourlyIncrease * DATEDIFF(HOUR, ur.CreatedDateTime, @DateTime))
FROM    UserResource AS ur
        INNER JOIN Resource AS r
            ON r.ResourceID = ur.ResourceID
WHERE   ur.ResourceID = @ResourceID
AND     ur.UserID = @UserID
AND     ur.CreatedDateTime < @DateTime
ORDER BY ur.CreatedDateTime DESC;

Then, if the user does something that uses up a quantity of 50, simply create a new record:

DECLARE @DateTime DATETIME2 = SYSDATETIME(),
        @UserID INT = 1,
        @ResourceID INT = 1,
        QuantityChange INT = -50;

INSERT UserResource (UserID, ResourceID, Value, CreatedDateTime)
SELECT  TOP 1
        ur.UserID,
        ur.ResourceID,
        Value = Value + (r.HourlyIncrease * DATEDIFF(HOUR, ur.CreatedDateTime, @DateTime)) + QuantityChange,
        CreatedDateTime = @DateTime
FROM    UserResource AS ur
        INNER JOIN Resource AS r
            ON r.ResourceID = ur.ResourceID
WHERE   ur.ResourceID = @ResourceID
AND     ur.UserID = @UserID
AND     ur.CreatedDateTime < @DateTime
ORDER BY ur.CreatedDateTime DESC;

This way you avoid needless transactions for things that are only for display purposes.

N.B. I've assumed the resource will only increment after a whole hour is complete, if this is not the case you may need something like:

Quantity = Value + FLOOR((r.HourlyIncrease * DATEDIFF(SECOND, ur.CreatedDateTime, @DateTime) / 3600))

That being said, for the purposes of the game, if this is going to be continually increasing it is probably best just extracting the 3 components (rate, time and starting value), and storing these within your session, and doing the calculation on the client side.