Creating a database with special items, like in Diablo

Question

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.

Answer 1

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
'item_attributes':

ID, strength, intellect, agility, price

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

Edit:
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.

Answer 2

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".

Answer 3

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.

Answer 4

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(...);  
    out.writeInt(item.customParams().length);
    for (long value : item.customParams()) {
      out.writeLong(value);
    }
    ... 
    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;
  }
}