Designing a flexible items system

When building an inventory system, there are a lot of different approaches that you can take. Some developers simply hard-code everything, while others make sure that nothing is hard-coded – and in between both of these two extremes is a comfortable middle point, where we have most of the flexibility of not having anything hard-coded, while still being able to develop with the same level of ease as if we had hard-coded it.

Designing an inventory system like this takes time – you can’t just throw everything together and hope it works. You need to plan it out first.

One question you need to ask yourself is: what about different item types? If there are weapons, armor, and general items, how will you handle that?

A hard-coded solution would be to create three tables – weapons,armor, and items. But at that point, you have a problem; how will you link specific items to something like monsters? You would need to create three more tables – monster_weapons, monster_armor, and monster_items. And then figuring out what particular item a monster might drop at any given time would be a major headache.

An easier solution to this is to create a single items table, with a ‘type’ column that can be used to track the type – that way, we only need to link monsters up to single items, and we can retrieve all of the monster’s droppables with a single, simple database query. Here’s what a simple items table might look like:

CREATE TABLE items (
	id int NOT NULL AUTO_INCREMENT,
	name text,
	type text,
	PRIMARY KEY(id)
);

At this point, we now have a single table that we can use to track our different items – and the flexbility to categorize them all by type if we so choose. This allows us to do something like this to create three different items(each a different type):

INSERT INTO items(name,type) VALUES ('Wooden Armor','armor');
INSERT INTO items(name,type) VALUES ('Wooden Sword','weapon');
INSERT INTO items(name,type) VALUES ('Red Potion','consumable');

By building our items table in this way, we only need one table to link a monster to the items that they will drop – and we can easily make them drop any type of item we want them to. We can also create an item_stats table, so that all of our items can have stats applied to them:

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

And this way, we can leverage our stats system to allow our items to have the same stats as our players or our monsters. We can easily add and remove stats for any item we choose – if a potion should have a defence of 4 and a maximum HP of 8, we can do that. The flexibility is there.

While it’s not the perfect system, this inventory system is ‘good enough’ – as long as the only thing you know is that you will need to add and remove item types, and have different items with different stats, this item system will work perfectly for you.

Do you know of a better way to design an inventory system? Shoot me an e-mail at buildingbrowsergames@gmail.com, or leave a message in the comments!

Edit: sepp has pointed out that the item.type column would be a perfect location to use what’s known as an enum. According to the MySQL documentation on enums, an enum is “a string object with a value chosen from a list of allowed values that are enumerated explicitly in the column specification at table creation time.” – which is perfect for what we need. Because we will only have so many item types within our game, using an enum will ensure that we can’t have situations where we make a typo and end up with an item with a type of ‘weapoh’ or anything. Here’s the table creation code, using a basic enum to set up 3 item types:

CREATE TABLE items (
	id int NOT NULL AUTO_INCREMENT,
	name text,
	type ENUM('Weapon','Armor','Usable'),
	PRIMARY KEY(id)
);

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.

Wednesday, July 2nd, 2008 SQL, code, database, design
  • I really like this solution. I was thinking how I could do the item system for a browser game idea I have and the big problem for me was that items should have individual stats and I did not want to have many complicated table structures because it would be more confusing and also it would not be good for the games performance. thank you so much for this idea - I thought about all the complicated ways but yours is so easy with the second table for stats depending on item types. Thanks!!!

  • keilaniswk

    What happens when you enter an item type that is not on the list?

    Its value will be left blank on the type column.

  • Because it's only you(the developer) adding items, this isn't really an
    issue - you just redefine your enum and go from there.

    While this isn't the best way to lay out an items table(and future tutorials
    will be doing it differently), it was the best at the time in terms of
    allowing us to be flexible with our items and stats when we weren't sure how
    our idea was going to grow and change.

  • Val

    If you want to add more enums, you need to redefine the table using the MODIFY TABLE syntax.

  • MrLollige

    Enum looks good. But google cant tell me how to add a possible value.
    For example, if I have a row:
    type ENUM('Weapon','Armor','Usable'),
    And I want to add 'Shines' and 'Tradeable' to it later on. How do I do that?

  • Hi Sunchaser,

    There aren't really any plans on building a quest system or a map-based movement system - but I'll do my best to research it and write something on it once I've figured out how to build one.

    sepp: using an enum for item type is a great idea - thanks for pointing that out.

  • Nice article...

    Do you plan to write some articles on a quest system editable by users or a system where players can move in a map?

  • For item.type I would use an enum.

    http://dev.mysql.com/doc/refma...

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