I'm working on a browser-based RTS, and we are targeting 10k+ users with multiple towns per user.

I have the following table townresources in which I store every resource value for every town ID:

CREATE TABLE IF NOT EXISTS `townresources` (
  `townResourcesId` int(10) NOT NULL AUTO_INCREMENT,
  `userId` int(10) NOT NULL,
  `resourceId` int(10) NOT NULL,
  `townId` int(10) NOT NULL,
  `balance` decimal(8,2) NOT NULL,
  `resourceRate` decimal(6,2) NOT NULL,
  `lastUpdate` datetime NOT NULL,
  PRIMARY KEY (`resourceId`,`townId`,`townResourcesId`,`userId`),
  KEY `townResources_userId_users_userId` (`userId`),
  KEY `townResources_townId_towns_townId` (`townId`),
  KEY `townResourcesId` (`townResourcesId`)

I'm a bit reserved about performance impact for a large amount of users. I'm thinking about moving the balance for resources to the towns table, and the general value of a resource to a PHP file. Is this a better option?

  • \$\begingroup\$ First: welcome to gamedev SE! It's a bit hard to give you an accurate answer as you didn't give much context. What kind of game is this? How many users or towns do you target? Give us more info :) \$\endgroup\$ – Laurent Couvidou May 22 '12 at 18:34
  • \$\begingroup\$ First is an browser based RTS, and we are targeting 10k+ users and multiple towns per user :). \$\endgroup\$ – Lixandru Iulian Adrian May 22 '12 at 18:41
  • \$\begingroup\$ OK, I've edited your question to reflect that. It should update in a while if it passes peer-review. Don't hesitate to edit your question yourself later on to add more infos. The more clear your question and the more likely you are to get good answers! \$\endgroup\$ – Laurent Couvidou May 22 '12 at 18:47

Performance wise: Yes. If you have a column for the various balances in one row, you will improve performance when looking up this information. Looking up and returning 1 row is faster than returning X rows and generating the totals from the compiled results.


CREATE TABLE town_totals (
   town_id  int(10),
   iron     decimal(8,2),
   gold     decimal(8,2),
   wood     decimal(8,2),

Design Wise: Academic DB design will say that this isn't a good thing to do. A design like this does become a problem if you are changing values or creating a large number of them. For resources, this likely isn't an issue... but take a look at say units instead. As you will likely be adding various units over the life of the game, having to change a similar table every time to create a new "totals" can be painful from a maintenance perspective, especially when the alternative allows you to create new options without touching your tables at all.

  • \$\begingroup\$ This is basically an Array of Structs instead of the Struct of Arrays like the OP has. It can be beneficial, but accurate timings are crucial to see if there is any benefit. \$\endgroup\$ – knight666 May 24 '12 at 7:50

I would suggest to add balances and resource rates to the table for towns and make the general values of the resources part of your PHP code base. Resource balances for a given town seem to be the kind of information that you will have to retrieve regularly, so improving the performance of this data retrieval is desirable.

This suggestion is based on a number of assumptions, though:

  • The types of resources that a town can produce are very limited, an integral part of the game design, and thus unlikely to change.

    If you are building a trading empire style of game, with lots of different goods and resources, then normalization (as in Academic DB design) will make your software easier to maintain and extend. This is way more important than speed improvements for some queries.

  • Resources are not updated very often.

    Your current database design allows for very efficient resource update commands, so your decision is also a speed and memory trade-off. I suggest to conduct two comparative benchmarks: the first to find out how much faster data retrieval of resource information is with resource information being stored in the town table, the second to find out how much faster updating resource information is with your current design. Then multiply the speed difference in the resource information retrieval test with the number of players that are online at peak times, and again with the average number of such data retrievals per player per minute. Do the same for the differences in the updating resource test by multiplying the speed difference with the number of such update commands per minute at peak times. Comparing the two resulting numbers should give you some insight on which database design performs better overall. Do both benchmarks with mock data for 10,000 players and as many towns as you would expect from an active player base.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.