# Creating a database with special items, like in Diablo

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.

• 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. – aaaaaaaaaaaa Apr 10 '11 at 20:03
• 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? – xpanta Apr 11 '11 at 11:27
• 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 – Marco Apr 11 '11 at 20:57

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.

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

• Thanks. This is going somewhere. Your suggestion seems logical. I will try to implement and maybe return with more questions (if any) – xpanta Apr 11 '11 at 11:25
• Additional table "item attributes" will be your bottleneck. – Andrey Frolov Apr 12 '11 at 7:20
• @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. – Marco Apr 12 '11 at 9:34
• 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 :) – Andrey Frolov Apr 12 '11 at 9:39
• @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. – Marco Apr 12 '11 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".

• 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? – xpanta Apr 11 '11 at 11:21
• 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. – Kylotan Apr 11 '11 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.

• 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. – xpanta Apr 11 '11 at 19:30
• For only 100 you might just treat them however you treat any other item, keep it simple. – aaaaaaaaaaaa Apr 11 '11 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(...);
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();