Designing databases: storing user stats in games(i.e. owned money) and 32bit integer overflow.

Question

Hello,

I am creating an little hobby database driven browser based game and I stumbled across this problem: I store money owned by users as an 32bit integer field (to be precise: two fields. One stores money in players hand, the other - money stored in bank). We all know, that maximum value, which can be stored in 32 bits is 2^32-1.

I am absolutelly sure, that 95% of players will not be able to reach the upper limit - but on the other hand (and after doing some calculations today) good players will be able to accumulate that much.

Having that in mind I came with the following ideas:

• store money in 64bits, which doubles space of each record.
• store money as string and convert to/from long long in the runtime.
• change game mechanics so players will not be able to gain that amount of wealth.

I know that existence of reachable upper limit is rather limiting for some players, so for me the third option is worst from the proposed ones.

Are there any other ways of dealing with this kind of problems? Which one would You go for?

Answer 1

Space on memory shouldn't be a problem depending on the amount of players you'll have simultaneously, but storing as string will definitely use more disk space.

But seriously, 4 294 967 296 rupees/simoleons/furlongs? Who are they? Sim Gates?

Answer 2

Why not store money the way it should be stored, as a Money data type? This is assume of course you are using SQL Server. The money data type won't have this limitation and won't be affected by rounding issues.

Answer 3

Taking an example from the real world, why not have different types of coins e.g a column for a million units of the currency.

Answer 4

Changing to a larger datatype is likely the easiest solution and considerations of disk space/memory aren't likely to be significant unless your game is huge in scale. Have 5,000 users playing your game? Changing from 32-bits to 64-bits will consume roughly 20k extra. That's not enough to lose any sleep over.

The best answer would likely come from someone familiar with how banks handle these types of situations, though their requirements may be far more complicated than what you need.