0
\$\begingroup\$

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.

  1. PlayerInventory( ItemTypes_ID, Player_ID, amount)
\$\endgroup\$
1
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Thanks a lot for your answer! I cant change my structure to xml or json at all, its to late for that :/ How exactly would the table "ItemType" look like ? It should be very dynamic at all, because different items should have different sets of attributes or certain "powers". Players should also be able to upgrade their "Sword" later, which grants them an Poision bonus on each attack for example. :) Thanks for your help ! \$\endgroup\$ – genaray Apr 1 '18 at 16:50
  • \$\begingroup\$ @genaray How versatile do you need your upgrade system to be? \$\endgroup\$ – Philipp Apr 1 '18 at 16:55
  • \$\begingroup\$ hmmm... It shouldnt be such versatile. Players should can only upgrade the weapon ( Sword +0 -> Sword+1 or later Sword+15 -> Sword+16) every upgrade increases the attack damage, futhermore they should be able to choose an metal for their weapon or armor ( The type of metal they choose will apply one or multiple different effects on each action, for example the poison metal in his sword will damage the enemy every few seconds, or the honed metal will just increase the damage each hit ). Thats all :) \$\endgroup\$ – genaray Apr 1 '18 at 17:04
  • \$\begingroup\$ @genaray In that case I would add two more columns to the PlayerInventory table, one for material and one for upgrade level. \$\endgroup\$ – Philipp Apr 1 '18 at 17:08
  • \$\begingroup\$ @Phillip And what would happen, if theres an item in the inventory, which isnt upgradeable at all ( Wood for example ) ? \$\endgroup\$ – genaray Apr 1 '18 at 17:15

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.