Moonbase

Been thinking about approaching allowances in MMORPGs lately. Some MMORPGs, Urban Dead for example, give the player a periodic allowance of some consumable resource (AP, in the case of UD).

What’s the best way to handle this? I’m hardly the first person to think about this stuff (the class of problems is pretty well explored), but it doesn’t look like there’s been much written online. So, I figured I’d post my own thoughts in case anyone finds them useful.

Updated: originally forgot to include residual ticks when changing tickspercredit; fixed now.

Getting Started

We’ll call our consumable resource “credits”, and give it its own separate table for simplicity’s sake. We’ll also stick with basic SQL and SQL datatypes. Full statements here are SQL; fragments aren’t necessarily.

Also, my SQL is admittedly kinda rusty, so I’d appreciate any corrections.

The Obvious Approach

The obvious thing is to create a two-column table: one column for the player id, and one column for their current balance.

CREATE TABLE credits (player INT NOT NULL, balance INT NOT NULL,
  PRIMARY KEY (player))

Querying, crediting and debiting a player’s balance are all trivial, and a periodic allowance can be handled by a scheduled job which simply executes:

UPDATE credits SET balance=balance+1

Capping Balances

However, UrbanDead also has a cap on a player’s AP balances. For a situation like that, simply modify your allowance SQL:

UPDATE credits SET balance=balance+1 WHERE balance < maxbalance

n.b. Unless you wanted to have per-player caps (in which case it could be another column), maxbalance would be a value subtituted in by your program rather than part of the SQL.

Don’t forget that you will also have to cap the balance in any other code which credits a player’s account.

Suspending an Allowance

If you need to be able to suspend a player’s allowance, you’d probably want to add a flag to the table:

ALTER TABLE credits (player INT NOT NULL, balance INT NOT NULL,
  suspended CHAR(1) NOT NULL DEFAULT 'N', PRIMARY KEY (player))

And grant allowances with:

UPDATE credits SET balance=balance+1 WHERE balance < maxbalance
  AND suspended = 'N'

Potential Problems

With respect to allowances, this simple approach has two problems:

1. allowance updates are large transactions which touch and modify the entire table (which could be bad if your MMORPG really is massively multiplayer)
2. everyone gets credited at once, which encourages load surges from players waiting for their next credit

Reversing the Problem

Let’s consider the problem from the opposite direction. If time is gil, then how about we track a player’s balance in terms of time? Here, we’ll track time in terms of ticks since an epoch (e.g. Unix time is seconds since the beginning of 1970).

CREATE TABLE credits (player INT NOT NULL, spent INT NOT NULL,
  PRIMARY KEY (player))

spent is a timestamp in ticks since the epoch—think of it as the point in time up to which the player has spent their allowance. This solves both of the above problems:

1. a player’s balance will reflect allowance credits automatically as the clock advances
2. everyone will get credited at different times, depending on when their initial balance was established

Querying, crediting and debiting a player’s account becomes a little more complicated, but not much.

n.b. now is the start time of the current transaction in ticks since the epoch; you should save it in a variable rather than re-querying the current time throughout the transaction. If it changed in the middle, it would mess you up.

• Balance: balance is ( now - spent ) / tickspercredit
• Set Balance: set spent to now - newbalance * tickspercredit + ( now - spent ) % tickspercredit
• Debit: increment spent by amount * tickspercredit
• Credit: decrement spent by amount * tickspercredit

Note that when we set the balance, we are careful to include the “residual” ticks, so the player won’t lose progress towards their next credit.

Capping Balances

Capping balances requires that you cap the balance when querying:

• Capped Balance: balance is MIN( maxbalance, ( now - spent ) / tickspercredit )

Then, instead of simply incrementing or decrementing, you debit or credit by setting the new balance to the capped balance minus or plus the debited or credited amount, respectively.

Changing Ticks-per-Credit

If you end up wanting to change tickspercredit at some point, you will need to update everyone’s balance accordingly.

UPDATE credits
 SET spent=now-(now-spent)/oldtickspercredit*tickspercredit
 +(now-spent)%oldtickspercredit

(now, oldtickspercredit, and tickspercredit would need to be substituted in by your program, of course.)

You needn’t worry about capped balances in this case.

Note that we are again being careful to include the “residual” ticks when updating spent; if we didn’t, everyone’s progress to the next credit would get reset, which would have the additional undesirable side-effect of synchronizing everyone’s “payday” to the same tick.

Suspending an Allowance

With the time-based scheme, suspending allowances still requires you to add a column:

ALTER TABLE credits (player INT NOT NULL, spent INT NOT NULL,
  suspended INT, PRIMARY KEY (player))

suspended is the time at which the player’s allowance was suspended. When it is non-NULL, you should use it in place of now in all your balance calculations. With an SQL implementation that supports COALESCE, you should be able to simply replace all occurrences of now with COALESCE(suspended, now).

Unsuspending a player is a simple matter of setting suspended to NULL and “spending” the time which passed while they were suspended:

UPDATE credits SET spent=spent+(now-suspended),suspended=NULL
  WHERE player=id

(now and id would be substituted in by your program—in this one case you really do just use now, rather than coalescing it with suspended)

Conclusion

The second approach to managing allowances makes for slightly more involved accounting, but it should scale much better as it doesn’t require modifying the entire table at once on a regular basis.