Building Browsergames

Designing your game’s database

One of the first things that I do whenever I start a new project is design the database.

In many ways, the database is the core of your game - without it, your game is nothing. You could write all the logic you wanted to, but if you didn’t have a database that logic would be worthless(there’d be nothing to use it on!).

There are many ways to design a database, and some are better than others. For this entry, I’ll be walking you through how to design a database that has as little data replication as possible.

Why don’t you want data replication? Because all of the data you’ll be storing in your database adds up. Suppose you have 5 Kb of data that gets replicated needlessly for every single player of your game. Your game moves along just fine, and slowly grows - until it gets picked up by a site like Digg or Boing Boing - at which point you have thousands of registrations in a single day. Suddenly, your playerbase has gone from being manageable(1000) to huge! And this is what will happen when you have data replication:

1000 players x 5 Kb = 5000 Kb (Roughly 5Mb)
100000 players x 5 Kb = 500000 Kb (Roughly 500Mb!)

Sure, 5 Mb of extra data is managable. But is 500?

That’s why you need to design your database properly. The less data you have replicated accross your database, the less problems you’ll encounter later on. Reducing data replication also helps when it comes to updating things - instead of having to update data in multiple locations, you can just update it in one place - and know for certain that wherever the data is accessed, it will be up to date.

With that in mind, let’s start designing a basic table structure for a browsergame.

We’re going to need users. And users will need a username, a password, and a unique ID(so that we can link them to things). For now, that’s all we’ll give our users:

1
2
3
4
5
6

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

If you run that pre under mysql, you’ll have a freshly created users table to play with. You might be wondering what the PRIMARY KEY line does - basically, it forces each row to have a unique value in that column. This will help ensure that we don’t ever have any conflicts like trying to create two different users with an id of ‘6′, for example. You can check out the Wikipedia article for more information about primary keys.

So now we have a users table. What else should our game have?

Most games have some sort of inventory that their players have, that they can use to store items in. Let’s set up tables for the items and the user inventories:

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE TABLE items (
	id int NOT NULL AUTO_INCREMENT,
	name varchar(200),
	description text,
	PRIMARY KEY(id)
);
CREATE TABLE user_inventory (
	id int NOT NULL AUTO_INCREMENT,
	item_id int,
	user_id int,
	quantity int,
	PRIMARY KEY(id)
);

And now we have all the tables in place to keep track of our users, and their respective inventories, with minimal data replication.

Suppose that, in our hypothetical game, the player picks up the Vorpal Sword of Hypothetical Existence. In order to add it to the player’s inventory, all we’d need to do is figure out what ID the Vorpal Sword had, and then add a new row(or update a row if they already had one of it) inside user_inventory.

The real benefit appears when it comes time to update something, however.

Let’s say that after your game’s userbase exploded, someone happened to find a bug: the Vorpal Sword, when used against a certain enemy, was way too powerful. You need to fix that, as fast as you can.

What do you do? Thanks to good database design, you only need to update it in one place! You can go into items, find the Vorpal Sword’s unique ID, and then update whatever attribute is causing the problem. You could even rename it, to the Nerfed Sword of Hypothetical Existence or something.

Because the important data for each item in the database is only stored in one central area, it’s easy to update it. But could you imagine what it would be like if you wanted to change the Vorpal Sword’s name, and it was stored inside user_inventory? For every single user who had one, you would need to change the name.

In the previous scenario, which would you prefer? Having to update rows upon rows of data, or just one? I know I would prefer to only update one, and I hope this has helped to communicate what you stand to gain by designing your database in such a way so that you only ever need to update one row.

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.

No comments yet.

Leave a comment