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.
ScriptableObjects. They may give you exactly the inspiration you need. \$\endgroup\$