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.

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.

Tuesday, April 15th, 2008 database, design
  • Roger Keulen

    Hi there !

    If you don't want your tables to large, you also can use `varchar(200)` except of `Char(200)`. But because it's variable, it's better top put it on the end of the field list. So the non variable lengths are first. If you want to speed op the finding and indexing change the varchar to fixed size char. Disks capacity is very cheap this days, also with hosting. And if you have data that isn't relational, just put it in a file. You don't need a database for all your data. Only a relational database for relational data, or special databases for reporting, transactions or other special stuff. But most of the time a file like XML will do.

    Like:

    <parseinbrowser>
    <classes>
    <thisuser_isalive>$FromScriptResult()</thisuser_isalive>
    <thisuser_monney>$FromDatabase()</thisuser_monney>
    $FromFile
    </classes>
    </parseinbrowser>

    $FromFile => <thisuser_info>....</thisuser_info> (Not really changing, like name, age, sex, location or other game stuff, like he is a dragon or what ever...)

    Html example with language !
    <div>

    You have[span class="thisuser_monney"][/span]Credit's on you account

    <div><script>parseXML("MyGame.php?info")</script>

    And without:

    <div>[p class="language_credits"][/p]<div>
    <script>parseXML("MyGame.php?Language=Eng&info")</script>

    Happy hacking.

    Sorry to hear, that you stoped with your site. Maybe i have to create my own or start a browsergame +Page on google.

  • tiwafic

    Thanks´Ż×

  • Very good meaningful article about database normalization and its benefits.

  • BUnzaga

    I have just started learning PHP/mySQL in the last couple weeks, and I am trying to follow along with your awesome tutorials. I noticed something, which I am hoping you could help me understand a little better.

    I noticed that you are taking an OOP approach to the items, stats, etc, but you didn't do this for user accounts. Is this a standard practice for databases, or was it just something that wasn't really thought about at the time you wrote this tutorial?

    As an example, if we followed the OOP approach, we would have two tables for user accounts. One would define what an account was, the other would be the list of accounts.

    Could you or anyone else tell me, if this would be a good or bad design architecture? I plan to have one account, with three character slots, which will be tied to the user account. I also plan to have a few other options, such as 'get_news', 'user_address', etc.

    Please bare with me and keep in mind the first time I have looked at this stuff was about a week ago when I was lucky enough to find your tutorials.

  • The reason that items got done this way(as opposed to user accounts)
    isn't really because of OOP at all, so much as flexibility - this
    tutorial started out without much of a plan for where it would go, and
    I wanted as much flexibility as possible when it came to adding new
    items.

  • daglees

    This is a very good introduction, it's down to earth and makes sense, thanks!

  • nikolas

    where do i write all this things!

  • SparK

    in your database! this is what the article is discussing

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