11
\$\begingroup\$

Let’s assume I want to create an RPG with collectible items my character can pick up and store in his inventory.

One item could be a sword, with an attack, defense, and level attributes.

Another item could be an apple, heal percentage, freshness, and servings attributes.

Both items are collectible items, and both objects can be stored in the players inventory, but they share separate sets of attributes.

I am trying to think of a way to set this up in the database. I originally looked to the EAV database model, so I could define a single collectible item entity, and bolt on separate attributes as I needed them, but I discovered EAV is slow because of multiple calls to the database to construct an entity, which wouldn't make it very suitable for a game in my eyes.

I also can't find any game development tutorials that use EAV, so there must be a more common solution I'm not aware of.

\$\endgroup\$
  • 4
    \$\begingroup\$ What kinds of DB are at your disposal? \$\endgroup\$ – badunius Jan 29 at 9:29
  • 1
    \$\begingroup\$ Are you sure you need a full-blown Database for that? Like, I can't imagine that you'd have so many items that any in-language collection wouldn't be able to handle it \$\endgroup\$ – Hobbamok Jan 30 at 12:53
  • \$\begingroup\$ Assuming you're using Unity like the tags suggest, I strongly recommend watching these two talks about ScriptableObjects. They may give you exactly the inspiration you need. \$\endgroup\$ – JesseTG Feb 5 at 1:54
38
\$\begingroup\$

When you are comming from non-game application development, then you need to be aware of a couple things.

  1. Relational databases have a niche role in game development. You really only need them when you have a massive multiplayer game where most of your players are offline most of the time. There are also some niche applications like online scoreboards or analytics. But it is pretty rare to handle any static game data like item attributes with them.

    The usual method to handle data like that is to load all the data you need at game start from a flatfile and then keep it in RAM while the game is running. I don't know how many items you have in your game and how much data they have, but unless your item database is in the order of hundreds of MB (I would go up to a GB if I target PC exclusively), you really don't need a relational database for them.

    When you do have the problem that your game needs to work with so much data that it doesn't all fit into RAM at the same time, then off-the-shelf SQL databases are not completely unheard of. But not only is this not actually a very common problem. If the problem does come up, then it is far more common to create home-brewed solutions for it.

  2. A more game-appropriate solution for a situation where you would use the Entity-Attribute-Value architecture is often the Entity-Component architecture. The item entity "iron sword" has the components "Tradeable" and "Weapon". The item "Apple" has the components "Tradeable" and "Consumable". The item "Chocolate Sword" has the components "Tradeable", "Weapon" and "Consumable".

    What kind of file format do you use to represent items with components? That's more of a religious question. But some data formats which are pretty suitable for hierarchical data like that are XML or JSON. I have an example for XML in this answer to a slightly different question.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ This answer surprises me. Is it normal to reimplement database-type features when a full DB isn't needed? Because at the very least player data must be persisted to disk. Is serialization to a file so much easier that it's worth the trade-offs (performance, consistency on disk, for example)? \$\endgroup\$ – piojo Jan 30 at 13:18
  • 3
    \$\begingroup\$ @piojo Games have to simulate the game world in real-time. Even when you are using an in-memory database running on the same physical hardware: just the interprocess communication overhead alone is often far too much for the purpose of games. I am not sure what exactly you mean with "database-type features", but if you are talking about indexes: Those are usually present in games in the form of lookup data structures like hash tables or binary search trees. \$\endgroup\$ – Philipp Jan 30 at 13:54
  • \$\begingroup\$ That's a good point about the performance requirements. My game is different than a P2P game where the server will be doing what you describe. The DB features are mostly what I mentioned: consistency on disk during writes, thread safety/locking, transactions/rollbacks, a set serialization format for all supported data, a schema for all data. \$\endgroup\$ – piojo Jan 30 at 14:08
  • 2
    \$\begingroup\$ SQLite might be a consideration in a scenario where you want to use a database, but performance is a concern. \$\endgroup\$ – shadowmanwkp Jan 30 at 15:02
  • 1
    \$\begingroup\$ @AlexDonnelly When serializing/deserializing a collection of polymorphic types, yes, you need a way to determine what each item's type is. A switch statement works, but it actually looks like Unity has support for polymorphic types out-of-the box. Take a look at this: forum.unity.com/threads/serializereference-attribute.678868 - It looks like all you need to do is mark your classes as [Serializable], and Unity will figure out how to determine if your inventory item is a sword, or a shield, or a whatever, assuming your class hierarchy is properly made. \$\endgroup\$ – Clay07g Jan 31 at 5:01
1
\$\begingroup\$

As I understood it, i would make a table "Inventory" this table would contain just things like "ID", "Reference to the item" and then also additional columns like "durability", "attribute1", "attribute2".

| improve this answer | |
\$\endgroup\$
1
\$\begingroup\$

While this depends a lot on the amount of your items and the data associated, I do not see much issue with using an EAV solution - anything currently in use will be stored in RAM as regular old objects, all the "heavy" database interaction happening only whenever inventory is loaded/unloaded as the player enters/leaves the game.

If you insist on more robust persistence and updating the "offline" inventory every time an item changes, normally that should still work quite well as databases tend to be quite quick at this sort of thing, even if every single item results in 10+ calls. This can be optimized, but by all means implement first and test performance later.

Another thing to keep in mind - you might want to keep a distinction between actual item instances (e.g. this specific Sword of Banishing that is enchanted for fire damage and has a gem socketed) versus item "templates" (e.g. "a Sword of Banishing can generate with 20-25 attack damage, randomly, sells for 300GP and can be recycled into Metal Scrap (Regular)").

A database would also integrate easily with other things, if your game would ever need a web interface or an item reference, for example.

What I have tried as well was fully separating the templates from instances and serializing the instances (i.e. actual items that are in your character's inventory) to a database but storing the templates as flat files that refer to the same properties as the serialized items (ideally, these properties are defined elsewhere and the game also refers to these definitions for the purpose of actual game mechanics).

| improve this answer | |
\$\endgroup\$
0
\$\begingroup\$

Games like Dark Souls save your inventory each time it changes. I don't know how they deal with app closing or crashing during a write, but I have tried randoms ALT+F4 and the system look robust to me. So the idea isn't new.

I, personally, prefer to keep everything in RAM and write to disk on save game, until that proves to be too inefficient (never happens). It has to be a really complex game to worry about that. And I prefer crude arrays or generic containers versus SQL apis.

Said all that, possible structures for tables in a data base:

You only need a table to save the status of your BAG, because item properties are classes definitions in your game code.

BAG Table
ID     SLOT     TYPE      COUNT_OR_HEALTH
1      0        Potion1   40        
200    1        VorpalSw  98         
3      2        Antidote  26

Explanation of the COUNT_OR_HEALTH column. Your game code knows that consumables like Potion1 and Antidote doesn't have health, so that field is used to save how many of those items do you have stacked into that slot. While Weapons aren't stackables, so that field means the health of the item (assuming game with repairable/breakable items). Add as many columns as you need. No need for EAV in my opinion, too complex.

Just note that tools exists to load most databases table files, like in the case of SQLite. Cheaters may find this and open the file to always have 99 potions. Obfuscation or another solution is required.

Definitions of items lives in your game code, as classes. You can use interfaces for the base types.

Very crude example:

interface Item
{
    string Type ();
}

interface Consumable
{
    bool Consume (Character target);
}

public class Potion : Item, Consumable
{
    protected string Name; // Name of the potion
    protected int    HP; // Recover of HP
    protected int    MP; // Recover of MP

    protected string [] ClearStatus; // Cure status like poison.
                                     // TODO: make status an enum


    public Potion (string name, int hp, int mp, string [] clear)
    {
        Name = name;
        HP = hp;
        MP = mp;
        ClearStatus = clear;
    }

    public string Type ()
    {
        return Name;
    }

    public bool Consume (Character target)
    {
        target.hp += HP;
        target.mp += MP;

        if (ClearStatus != null)
            for (int i = 0; i < ClearStatus.Length; i++)
        {
            target.RemoveStatus(ClearStatus[i]);
        }

        Simulation.instance.Changed();
        return true;
    }
}

public class BagSlot
{
    public string Type = "";
    public int    Count = 0;
    public int    Health = 0;
}

// Incomplete class, only an example
public class Simulation : ScriptableObject // or MonoBehavior attached to main camera if you need Update
{
    // All potions of the game
    protected Dictionary<string, Potion> Potions;
    protected void InitPotions ()
    {
        Potions = new Dictionary<string, Potion>();

        //          Name in map            Name       HP    MP    Clear Status
        Potions.Add("Potion1",  new Potion("Potion1",  200,   0,  null));
        Potions.Add("Potion2",  new Potion("Potion2", 1000,   0,  null));
        Potions.Add("Ether",    new Potion("Ether",      0, 100,  null));
        Potions.Add("Antidote", new Potion("Antidote",   0,   0,  new string [] {"Poison"}));
    }

    // All weapons of the game
    protected Dictionary<string, Weapon> Weapons;
    protected void InitWeapons ()
    {
        Weapons = new Dictionary<string, Weapon>();
    }

    // The BAG
    public List<BagSlot> Bag = new List<BagSlot>();

    // (...)
}

Many possible designs exist.

Different thing is if you want a way to describe items because you will have a growing itempedia. Doing that is a lot of trouble, and a better way would be to expand the itempedia with each game update, always including the new items as hardcoded lists/maps/arrays.

The BagSlot class can hold any type of item, an keep track of stacking and durability (if you want durability). We don't like complicate type casting here and there, we prefer a simple string telling us the name of the item in that slot.

Usually, games don't allow duplicated names, no matter if items are of different type. We use that to effortlessly find the correct item, no matter its type.

When player enter the consumable item menu, we iterate the Bag and only return consumables.

When player is in the equip menu, we iterate the bag and only return weapons, armors, etc.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ If I was going with the COUNT_OR_HEALTH approach I'd call them param1, param2, param3, .... Which is a valid approach, but maybe not the ideal one. Calling it COUNT_OR_HEALTH just means you'll need to rename it every time you add an item type which needs a different attribute that isn't count or health. Eventually you'll call it COUNT_OR_HEALTH_OR_STRENGTH_OR_FIRING_SPEED_OR_DURABILITY_OR_... \$\endgroup\$ – user253751 Jun 26 at 11:44
  • \$\begingroup\$ @user253751 Yes, that situation may happen. But we have the best possible use of storage. This only works if you are very sure your items will have only one numerical property. If some items will have two, then yes, you need PARAM1 and PARAM2 columns. If some will have three... and so on. Name of the column isn't really important. \$\endgroup\$ – Hatoru Hansou Jun 27 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.