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.

what do you exactly mean with manage. manage the update in the database, or in the gui? — SomeJavaGuy, Sep 04 '15 at 12:02
@KevinEsche i mean in database because if someone attacks the city how much resouces are available to be captured. — Developer Nation, Sep 04 '15 at 12:11
If you know the current resource status and the time it was saved, you know the ratio. Just do Value+timedifference*ratio to get current value. That way you only have to update when something actually is changed due to "attacks" or spent on local constructions/troops. Watever your games has. — mxix, Sep 04 '15 at 12:13
@mxix that is the exact approach i am trying but is there a better way for that? — Developer Nation, Sep 04 '15 at 12:15
I can't see a better approach than what you are doing. Just wrote out an answer describing exactly this approach, then saw your edit... Doh! — GarethD, Sep 04 '15 at 12:24

score 4 · Accepted Answer · answered Sep 04 '15 at 12:35

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.

Flooring the value will result in missing resources. @GarethD what would you suggest to tackle this issue. Save double/floats in DB and floor the value in the frontend/application itself? — Davy de Vries, Feb 13 '20 at 13:59
@DavydeVries it is only the total number of the "resource" that is being floored, so the resource loss would be minimal. For example, if it was 52 minutes and 23 seconds since a "wood" resource was created, and "wood" is replenished at a rate of 100 per hour, this would mean 86.67 "wood" have been replenished, all the floor does is round this down on the assumption that wood comes in whole increments. If this is not the case, then yes, exactly what you said, store it as a decimal and don't round down and do the rounding on the front end — GarethD, Feb 13 '20 at 14:58

Best way to manage resources in a game

General Game Information

Problem

Why update resource value in database?

My Current Approach

1 Answers1