Best way to store items with statistics in an mysql database?

Question

Im currently working on an multiplayer game. Therefore using MySql for my game to store different data ( For example players, resources and so on ).

Lately i implemented items, players receive them by gathering resources for example, later they should also be able to craft them or even equip them if its a sword or an armor piece.

My problem is that due to my small experience with MySql, i have no clue how to add different statistics ( Equipable, Damage, Different Effects and so on ) to the items. Not every item should have them ( For example "Wood" shouldnt have statistics ). How should i modify my tables ? Any help would be appreciated ! Thanks !

My current table structure looks like this :

1. ItemCategories ( ID, Name )
2. ItemTypes ( ID, Name, ItemCategories_ID )

---

For more information, how i actually manage/spawn those items in... here two more important tables.

This table contains resources ( For Example "Basic Tree" ) and the item it drops when gathered.

1. Resources( ID, Name, ItemTypes_ID, gatheringTime, gatherAmount, spawnWeight)

This table contains the items in the players inventory.

2. PlayerInventory( ItemTypes_ID, Player_ID, amount)

Answer 1

A relational database is a great solution for persisting transactional data (data which changes during the game). But they are not necessarily that ideal for persisting static data (data which does not change during the game). An alternative solution you might want to consider for your item types is to store them all in a file which you load completely into memory when your gameserver starts up. This allows you to use a less structured format for it, like JSON or XML.

But if you are really determined to store your item properties in a relational database, then I would recommend to have one ItemType table which stores only the information used by all item types and then separate tables for each category with additional fields unique to items of that category. Like a table Weapons ( ID, MinDamage, MaxDamage ... ), a table Armors( ID, Slot, Defense ... ) and so on.

Another option would be to have a table ItemProperty(ID, Property, Value) where Property is the name of the respective property. So when you want the attack of the item #737 you would do a query like SELECT Value FROM ItemProperty WHERE ID = 737 AND Property = 'Attack';. This schema allows you to add any arbitrary item properties to any item without having to alter any tables. But it's neither very type-safe (you can accidentally add properties which are meaningless) and it is not very query-friendly either.