architecture - Database for a diablo 2 like item system?

Diablo 2 has a very sophisticated item system and I want to create something similar. But I am having a hard time getting the architecture right. In Diablo 2 most different kind of gear has different kind of attributes and they can all hold X amount of modifiers depending on the quality.

However my server creates items randomly based on several factors. And each item is pretty much unique. To lower the amount of records a player can only have X amount of items. I tried to get some thought on "paper" and would like some insight in how this can be done properly. These are my findings:

Having all my different kinds of gear separated since weapons have damage, boots alter speed and armor, shield give armor and block rate, etc. A modifier table holds all the modifiers and has a FK to weaponId, bootsId, shieldId and only one should be set where the others are null. I could add requirements in the main tables as well but in the diagram below I opted for a separate 1 to 1 relation table.

And this could be a variation of it where there is a single table linking them together.

To clarify table modifiers, the column modifies should hold something like undead and percentage could hold 10 to make the armor or weapon 10% more effective to undead. Or strength & 5 to increase strength by 5.

But I might be over complicating things. I could have a single item class that defines the type, damage, armor, etc and keep them null if that attribute does not belong to the item. I could even throw in the requirements since thats a 1 on 1 relation anyway. The modifier relation would stay the same where a single item can have more modifiers. Something like this:

But this made me wonder, why would I store any attributes in the actual item table? Damage could be a modifier too and so can defense. I would handle it like so:

modifies = "baseDamage"
amount = "10"

modifies = "bonusDamage"
amount = "3"

Now I can show the player what the base damage of the weapon is and how much the bonus is. Heck, I could even handle the item name and description using this and combine them all by coding in there proper classes. This would make it really flexible where a stat like strength in code is just a String and an int.

The downfall is a lot more coding and calculations and a large table since each item has at least a 3 or 4 of modifiers. With 10.000 players all holding 100 good items with like 10 modifiers this would translate into 10.000.000 records. Nothing that MySQL could not handle but it does make me wonder about performance a bit.

I really need some advice or maybe a whole different view on the topic to make this work properly.

Edit

I was thinking about something a bit different. If I would make a class to hold a property, or even stats and store that as a binary field alongside the item? Would this be a good solution? Take a look at the following code:

public class WeaponExample {
    Weapon weapon = new Weapon();

    public WeaponExample()

    {
        weapon.name = "sword";
        weapon.damage = 10;
        weapon.levelRequirement = 4;

        //create a modifier that adds extra strength to the character
        Modifier modifier1 = new Modifier();
        modifier1.type = "strength";
        modifier1.amount = 5;


        //create a modifier for 10% extra damage against undead
        Modifier modifier2 = new Modifier();
        modifier2.type = "undead";
        modifier2.percentage = 10;

        //add them to the weapon
        weapon.modifiers.add(modifier1);
        weapon.modifiers.add(modifier2);

        //serialize weapon.modifiers to store in database.

        //..
    }    
}

class Weapon {
    //Basic weapon data
    public String name;
    public int damage;
    public int levelRequirement;
    //... etc


    //Data to be serialized to binary and stored
    List modifiers;
}

class Modifier {
    public String type;
    public int percentage;
    public int amount;
}

Serializing those 2 modifiers generates a byte array of 183 bytes. This will likely be a bit larger as I add some extra stuff. I will do some more testing. Now I am by far an expert on these things but if I would have a separate table I would end up with:

• 2 varchars of 16 bytes (14 characters + 2 overheat).


• 2 ints of 8 bytes (since the others will be null?).

That makes 24 bytes instead of the whopping 283. So I'm thinking this is not such a good idea. This does save me a lot of looking up in the SQL table and if I deserialize on the clients end then my server does not have to do the job of deserializing all those items when requested.

Answer

Given your requirements I would recommend not using a RDBMS. I'd look at a JSON type document database for storing your data. It tends to be me more flexible and matches the data structures you'll design in a cleaner way.

If you do want to use an RDBMS though I'd look at PostgreSQL rather than MySQL. PostgreSQL 9.4+ has JSON binary support. You can essentially design a table that looks like characters(id serial, inventory jsonb). With the database module plv8 you can write Javascript functions to operate on the objects. As an example your inventory data could just look like:

[
    {
        "name": "sword",
        "damage": 10,
        "levelrequirement": 4,
        "modifiers":
        [
            {                
                "type": "strength",
                "amount": 5

            },
            {                
                "type": "undead",
                "percentage": 10
            }
        ]
    }
]

In plv8 you can implement trade operations between players and modify or delete items. It's up to you though how much you do on the database and how much you do on the server.

create or replace function add_item(character integer, item json) returns json as
$$
    // No error handling is done here. Good idea to try/catch
    plv8.subtransaction(function()
    {
        var inventory = JSON.parse(plv8.execute('select inventory from characters where id=$1', [character])[0].inventory);
        inventory.push(item);
        plv8.execute('update characters set inventory=$2 where id=$1', [character, JSON.stringify(inventory)]);

        // Can even setup database notifications so others can listen for events

        plv8.execute('notify inventorychanged, \'{ "character": ' + character + '}\'');
    });
    return { success: true };
$$
language plv8 stable strict;

I should mention you can still setup indexes and query json binary objects in postgresql. Also since jsonb is binary it's fairly compact. Not as compact as you'd probably like, but it's manageable.