Designing Browsergames: a flexible stats system

Most (if not all) of the browsergames I have played have, in some way or another, incorporated some sort of statistics for the player. These might be things like attack, defence, strength, etc. – or they might be more ‘abstract’ stats, like willpower, moxy, or ego.

At any rate, pretty much every game has stats in it – otherwise, there’s no way of knowing how well you’re doing in comparison to anyone else!

Over the course of designing your game, you might figure out that you need 3 stats – attack, defence, and magic. And so you’ll set up the ‘users’ table in your database something like this:

CREATE TABLE users (
	id int NOT NULL AUTO_INCREMENT,
	username varchar(250),
	password varchar(50),
	attack int,
	defence int,
	magic int,
	PRIMARY KEY(id)
);

Which would be fine. You’d build your game, and the game would work as expected.

But what happens when, later on down the road, you want to add a new stat – like magic defence or something? With the design that you have now, you’ll need to first modify your users table, and then modify any code that interacts with the new stat – in addition to initializing the stat for all the players who already exist but don’t have the stat.

Does that sound like a bit of a pain to you? Because it does to me – and I’m here to show you a better way.

By moving our stats into their own table, we can create a table filled with available stats for players, without needing to worry about modifying any tables later on in our game – if we want to add a stat, we just add it to the table and our code, and we’re done – we could even build something in place to check to see if users have the stat already, and if they don’t it creates the entry for them. That sounds better than having to modify our database by hand, doesn’t it? Here’s how we could set up our initial stats table:

CREATE TABLE stats (
	id int NOT NULL AUTO_INCREMENT,
	display_name text,
	short_name varchar(10),
	PRIMARY KEY(id)
);

And this table will now store all of our stat definitions. If we wanted to add a stat called “Magic Defence” (and abbreviated as “mdef” for our code’s sake), we could run a query like this:

INSERT INTO stats(display_name,short_name) VALUES ('Magic Defence','mdef');

…At which point the new stat would exist in our system – and all we would need to do is modify our code to interact with it.

In order to store the stat values for a player, all we need to do is create another table:

CREATE TABLE user_stats (
	id int NOT NULL AUTO_INCREMENT,
	user_id int,
	stat_id int,
	value text,
	PRIMARY KEY(id)
);

At this point, we’ll have three tables – users, stats, and user_stats. The purpose of user_stats is to link particular users to particular stats – and store their values. user_stats.stat_id is the value of stats.id for a particular stat, and user_stats.user_id is the value from users.id. user_stats.value will store the actual value of our stat.

You might be wondering why the type of user_stats.value is text, as opposed to int or something – it’s so that we can have as flexible a stats system as we need. By storing the value as a text type, we’re able to store text or numbers inside our column – which means that if we wanted to have a stat like “Status” that had possible values of “Awake”,”Asleep”, or “Eating” – we could, in addition to having another stat called “Restedness” that simply contained a value between 1 and 100. By using the text type for our column, we’re able to store whatever we want – although this does mean that you need to be more careful about how you handle the values of your stats within your actual code.

Hopefully by taking a look at the database structure and these explanations, you can see why this system might be a better way to store player stats than by hard-coding them into your users table – although really, any approach will do. But if you’re looking for flexibility down the road, this approach will give you more of that than hard-coding values into your database. Flexibility is always a benefit if you ever plan on tweaking your game.

Wish there was more?

I'm considering writing an ebook - click here.

.

Luke is the primary editor of Building Browsergames, and has written a large portion of the articles that you read here. He generally has no idea what to say when asked to write about himself in the third person.

Monday, May 5th, 2008 database, design
  • Siem Wigger

    Thanks,
    That creative way of thinking about how to order a mysql database really is a great way of doing things!

  • Smitty927

    When dealing with a larger player base, having a separate table structure for stats only is the way to go! The reason for this? Simple really. If it is in the user table, then you are running queries on the stats data on every page load, it would be more demanding on the database.

    To eliminate the need for querying 3 tables, use a select statement similar to what Anubis has pointed out, but simply create a new view of the 3 tables with it and then query that view.

    ie:

    create view user_stats_view
    as
    SELECT * FROM `users`, `stats`, `user_stats`
    WHERE `user_stats`.`userid` = `users`.`id` AND `user_stats`.`statid` = `stats`.`id`;

  • Mark

    What method would be faster? Having the stats in your user table is one query less right?

    I tend to select all data from the user in my header using a mysql_fetch_array so I can use things like $user['credits'] or $user['id'] wherever I want. Is this a bad habit? I figured you were gonna use the user data on almost every page anyway.

  • Chances are when it comes to performance that selecting your stats directly
    from your user table will be faster - however, if you ever want to add(or
    remove) a stat you will need to make changes directly to your user table.

  • It would actually be just as fast to do it this way as in 1 table... for example:

    3 tables, users, stats, user_stats (to use the example tables)

    I want to grab the stat "Attack" for the user with ID 1.

    Attack's ID = 1, User ID = 1. SO

    SELECT `user_stats`.`value` FROM `user_stats`,`stats`,`users` WHERE `user_stats`.`userid` = `users`.`id` AND `user_stats`.`statid` = `stats`.`id` WHERE `users`.`id`=1 AND `stats`.`id`=1 LIMIT 1;

    And there you have it.

  • nice article! nice site. you're in my rss feed now ;-)
    keep it up

  • Hi Scion,

    I am not quite sure I understand what the benefit of using the two different types of columns would be - especially in a loosely typed language like Perl or PHP. Whether the user retrieves a number or a word, that value will still be loosely-typed after it's been retrieved - which means the issue can bite the user either way. How would changing the number of columns in the tables fix that?

  • Scion

    I would caution against mixing types in the same field, If you do sooner or later it WILL come back to bite you.

    If you want to have some stats that are numeric and some that are text based there are several possible approaches.

    One would be to replace the value field with two fields one numeric that other text...and have a third Enum typed field to indicate which of those contains the value.

    Another aproach would be too have two seperate tables one for text based stats and the other for numeric stats.

    although im not convinced for the need for text based stats...theyre not really stats...theyre..ummm...something else? :)

blog comments powered by Disqus

About

Building Browsergames is a blog about browsergames(also known as PBBG's). It's geared towards the beginner to intermediate developer who has an interest in building their own browsergame.

Sponsors

Got Something to Say?

Send an e-mail to luke@buildingbrowsergames.com, or get in touch through Twitter at http://twitter.com/bbrowsergames