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.

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.