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.