Game resources storing

Question

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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Stores Town Resources' AUTO_INCREMENT=9;

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?

Answer 1

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.

Example:

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

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.

Answer 2

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.