I am in the middle of creating a browser game. I need to add 'special items' like in Diablo, but simpler. Item names are fixed (e.g. 'ring of power'), so only their attributes change.

In my game there are different towns. Each town may trade a different set of items with randomized characteristics. For instance, in Town A a 'ring of power' will have better attributes than the same item in Town B (and, thus, it will cost more).

My question is how to construct the database to handle this.

After constructing the database I will create a small script that will assign a different subset of items in each town. Each item with random attributes and a price that reflects its value.

I can make the script; what I need help on is creating the database.

  • \$\begingroup\$ Single player or multi player? Does the game run in the browser or on the server? You might not at all need a conventional database for this. \$\endgroup\$ Apr 10, 2011 at 20:03
  • \$\begingroup\$ This is interesting and I haven't thought of that before. It is a multiplayer game that runs on a browser (no flash required). Why do you say that I might not need a conventional db for this? \$\endgroup\$
    – xpanta
    Apr 11, 2011 at 11:27
  • 1
    \$\begingroup\$ You can put your data in files. A database is slower than parsed config files, I think. But the data structure is the same as in dbs. You should also consider to use memcached for php or the global scope in java to cache the items. In this case the save location doesn't matter since you cache your static data, which prevents you from always querying the db or reloa \$\endgroup\$
    – Marco
    Apr 11, 2011 at 20:57

4 Answers 4


You can think about items as a pair of names with description and attributes (including the price)
You can do a table 'items': ID, descid, attrid

You can make the two others like this:
'item_descriptions': ID, name, desc

ID, strength, intellect, agility, price

This would split up the description part and the, let's say, value part of the item.

I would, as said in a comment, now recommend the following structure:

'items': ID, descid, strength, intellect, agility, price
'item_descriptions': ID, name, desc

The item is in a table with the attributes, because every attribute set should be unique.

The description is in another table, because you wanted to have the same description for different attributes.

And one thing in addition:
I strongly recommend to use memcached! With it you don't have to query the database all the time. Memory is much faster.

  • \$\begingroup\$ Thanks. This is going somewhere. Your suggestion seems logical. I will try to implement and maybe return with more questions (if any) \$\endgroup\$
    – xpanta
    Apr 11, 2011 at 11:25
  • \$\begingroup\$ Additional table "item attributes" will be your bottleneck. \$\endgroup\$ Apr 12, 2011 at 7:20
  • \$\begingroup\$ @Andrew Frolov: When he uses memcached it shouldn't be a performance problem. But I thought about it. Every item should have other attributes, so this additional table doesn't make sense. I will edit my answer. \$\endgroup\$
    – Marco
    Apr 12, 2011 at 9:34
  • \$\begingroup\$ Look at my answer below. I think you'll like it. I have already implemented this approach in my MMO game. And it works well without memcached :) \$\endgroup\$ Apr 12, 2011 at 9:39
  • \$\begingroup\$ @Andrew Frolov: Yes, this would work great. But only for items with unique description and name. And multiple items with the same description is, what the asking person wants. \$\endgroup\$
    – Marco
    Apr 12, 2011 at 9:47

The problem here is you are trying to key the item on the name "Ring of Power" and thus running into a conflict. Instead, use a hidden key for the item, multiple items display as "Ring of Power".

  • \$\begingroup\$ Thanks. I am sorry but I can't really understand what do you mean by 'hidden key' is this a database-related term? Can you be more specific? \$\endgroup\$
    – xpanta
    Apr 11, 2011 at 11:21
  • \$\begingroup\$ The technical term is a surrogate key (en.wikipedia.org/wiki/Surrogate_key) and usually it just means a unique number, and is often called 'id' or something similar. So you might have a weak Ring of Power with id=1 and a strong Ring of Power with id=2. \$\endgroup\$
    – Kylotan
    Apr 11, 2011 at 11:55

If it's multi player it's going to have to run on the server, you can only export the interface to the browser without allowing people to cheat.

If your generation script is fast enough and made as an ID->item formula you may simply generate the item whenever needed. You could also store the entire list as a literal in a code file. I'd suggest using one of these methods for the client code.

For the server code, if you are using a stateless language like PHP a literal list may be a lot of strain on the server as it will have to be loaded once for each request, so in that case you would have to use on-the-fly generation, or if that is not practical either a database.

Your typical relational database is very slow, that is why I'd as far as conveniently possible would try to avoid using it.

  • \$\begingroup\$ yes, you've got a point here. It is my fault though that I didn't mention that I won't be having more than 100 items. It is not a game that its gameplay depends on owning these items. Special Items are just an extra feature. So 100 (more or less) will be enough. This number is not going to produce much overhead. However I will consider that option. \$\endgroup\$
    – xpanta
    Apr 11, 2011 at 19:30
  • \$\begingroup\$ For only 100 you might just treat them however you treat any other item, keep it simple. \$\endgroup\$ Apr 11, 2011 at 20:13

Items is the most high loaded part of game database. So you should create the most effective design to handle it. In your case it will be table like this one

   create table item (
    id long primary key
    item_name string
    item_state_type int
    item_state_data varbinary 
id - it is the key for unique item entity.
item_name - "ring of power"
item_state_type - type of your item. Ordinary or random generated.
item_state_data - serialized custom data. In your case it is an array of custom attributes. 

The pair of tables item and item_attributes with keys-and-values isn't a good idea. Operation with one row (in my design) will be converted in operation with ~10 rows in different tables (in key-value design)

For example

id, item_name,      item_state_type, item_state_data, owner_town, owner_id
1, "ring of power" , 0, null,    1,    null - vendor ordinary item
2, "ring of power" , 1, binary1, 1,    null - vendor customized item
3, "ring of power" , 1, binary2, 2,    null - vendor customized item in another town
4, "ring of power" , 1, binary1, null, 10 - user1 item
4, "ring of power" , 1, binary2, null, 10 - user2 item

Actually I prefer to store item templates in XML files, but this approach is OK too for simple data model.

item_state_data - serialized custom data. In your case it is an array of custom attributes. The main idea - you should somehow translate your custom data into one byte array. Or into string.

Example in java + pseudocode.

public void saveItem(ItemDataSet item) {
      PreparedStatement stmt = getConnection().prepareStatement("insert into item ... ");
      ItemSerializer serializer = SerializerFactory.createItemSerializer();
      byte[] item_state_data = serializer.serialize(item);
      stmt.setBytes(1, avatarInfo);

public ItemSerializer {
  public byte[] serialize(ItemDataSet item) {
    DataOutput out = new DataOutputStream(...);  
    for (long value : item.customParams()) {
    return out.toByteArray();

  public ItemDataSet item deserialize(byte[] bytes) {
    DataInputStream in = new DataInputStream(bytes);  
    ItemDataSet item = new ItemDataSet();
        int arrayLength = in.readInt();
    for(int i=0;i<arrayLength; i++ ) {
      item.addParam( in.readLong);
    return item;
  • \$\begingroup\$ this is also a very interesting implementation. Would you be so kind in giving more details on these binary item_state_data? How will it look like? \$\endgroup\$
    – xpanta
    Apr 12, 2011 at 11:40
  • \$\begingroup\$ Example added.. \$\endgroup\$ Apr 12, 2011 at 12:10
  • \$\begingroup\$ sorry for my late response (too much work). Thanks for your code. I use Java a lot (not for this project though) and never used it to serialize objects into the db. Now I know (i am not a pro as you can see). But I need to ask how easy it is to add new attributes or remove others if needed, with this way. \$\endgroup\$
    – xpanta
    Apr 14, 2011 at 6:58
  • \$\begingroup\$ Not so easy as if it would be a simple table, because you will have to convert all you data from old format to the new one. But we are talking about items - the most loaded part of database. 70% of operations involves items. You should do everything you can to optimize your database in this part. \$\endgroup\$ Apr 14, 2011 at 7:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .