# How do I store Items in MySQL?

I'm working to my first game containing items (built a small one before, strategy type but not very complicated). I was thinking something like this for the db design since it will be almost the same items in each shop. I'm thinking of connecting the shop to user items (not sure if you understand what i'm thinking of):

shopname
shopNameId, shopName
shopCategory
shopCategoryId, shopNameId shopCategoryName,
shopItems
shopItemId, armourId, oneHandedWeaponId, twohandedWeapoinId,


this would be for the shop..now;

oneHandedWeapons
oneHandedWeaponId, weaponName, weaponMinAtk, weaponMaxAtk, ...


otherAttributes,

oneHandedExtendedAttributes
oneHandedExtendId, oneHandedWeaponId, ... otherAttritubes,


and again for other item types. This npc's will be the only place from where they can buy items.

Now when a user bought a new item from one of the shops i should insert the id of that item in the inventory table no? I was thinking on a structure like this for a stash based on 10 slots.

userStash
userStashId, userId, slot1, slot2, slot3, slot4, slot6, slot7, slot8, slot9, slot10


now on each on slot i can insert the id of that item.

Now my questions:

1. Is it ok how i did this?
2. Do you see any problems?
3. Do you have any advice?
4. Can you give me another ideea / another example how should I do this part?
• Are the users always going to be limited to just 10 items? – thedaian Oct 3 '11 at 15:47
• no this is just an example. thank you for editing forgot to quote everything – Bogdan Oct 3 '11 at 17:29
• perhaps change the question to 'How do I store items in a Database' instead of MySQL. so far this is not MySQL specific – Simurr Oct 4 '11 at 19:55

While this proposed implementation is workable, it's not very scalable -- and scalability should be one reason you consider using something MySQL. You don't need a "database" to store items and shop/vendor data for a game, especially not a simple single player game. Simply storing the data in flat files (text, or XML, or some binary format you invent) would work just as well and would likely be much easier to implement and maintain. You should not use a database like MySQL unless you need something a database offers that no other solution does. I don't believe you do.

That said, some specific critiques:

• shopCategory is designed such that every category is unique to a shop. The "swords" in shop A are not the same category as "swords" in shop B. This is probably not desirable as it will likely introduce complexity that isn't needed; it also creates data bloat. Categories probably don't need to be tied to a shop.
• shopItems implies that there is one item set per shop (although there appears to be no relation between shops and the item set), and that an item set contains exactly one of each fixed class of weapon. It is impossible for a shop to see two different kinds of two-handed weapons.
• Which begs the question, if the weapon class if fixed in table design, why is there a 'category' table at all?
• The user "stash" table implies a fixed upper limit to player storage, which would be very difficult to change in the future. This is a bad idea.

This sounds like your first foray into both games with item systems and database design, which is another reason I would recommend you avoid a database entirely. The system you want to model has multiple many-to-many relationships, which aren't immediately obvious how to capture to the DB neophyte.

What you need is an intermediate table. Let's say you have a table defining a shop:

Shop
------------------
shopID   | integer
shopName | text


and one defining every item in the game:

Item
------------------
itemID   | integer
itemName | text
itemType | integer
...


You can have a third table defining the (many-to-many) relationship between shops and the items they carry; each entry in this table represents a specific instance of a specific kind of item (from the items table) for a specific shop (from the shops table).

ShopInventory
-------------
shopId | integer (the shop ID that owns this item)
itemId | integer (the ID of the item)


So if Bob's Shop is shop ID 23, and it has 4 copies of Awesome Longsword (item 2) and two copies of Potion of Hangover Resistance (item 73), the ShopInventory table might look like:

shopId | itemId
---------------
23     | 2
23     | 2
23     | 2
23     | 2
23     | 73
23     | 73
... etc ...


This is a really, really cursory overview of many-to-many relationships and how you might implement them -- they are really needed to do the kind of model you're talking about well, so I would strongly encourage you to research DB design a lot more if you really want to use a DB for your items. But I would encourage you more to simply not bother with a database at all. Store your data in text files or XML files while you figure out how to best design you item/inventory/shop systems.

EDIT: Definitely read through the comments to this answer if you're actually interested in the database implementation bit, because my initial example was quite cursory and a number of users have pointed out a number of simple but important improvements that could be made to it.

• +1. Something to point out, though, is that if you're making a web based game, then it's perfectly fine to use a database, even if it's mostly single player. But yes, if your game isn't web based, then don't use a database, it just makes things more complicated. – thedaian Oct 3 '11 at 16:27
• i'm looking for database driven because it's web based game and yes it's my first time trying this, with db design i'm familiar but i lack of experience working with fk's makes me confuse and the entire ideea is making me the same because i'm not sure about it and this is the reason why i came to ask questions to see a better example, model, ideeas etc. the items per each shop will be the same only the price will be different and the name of the shop. the entire thing i consider it a challange and opportunity to learn something new. :) the ideea you suggested in your post i will give it a try. – Bogdan Oct 3 '11 at 19:18
• and see how it goes. – Bogdan Oct 3 '11 at 19:18
• Nice answer, I like the Hangover Resistance Potion. You could also have a "quantity" in the many-many relation table instead of adding an entry for every item. – bummzack Oct 4 '11 at 6:52
• @ bummzack yes the hangover resistance potion is nice :) wish sometimes i had one of those :)) – Bogdan Oct 4 '11 at 13:35

1 No... At least not how I would have done it. It might be exactly what you need, but I don't really see the logics.

2 Yes a few. For example:

• Your user stash violates 1NF principles: Should not have multiple columns for storing the same data domain (but it's not the most important thing).
• Your shopItems is not efficient if each item isn't composed exactly of an armor, a one handed weapon and a two-handed weapon. Which is not the case presuming on handed weapons are not always 2 handed and the other way round.

3 You should probably change your data structure or use a more dynamic storage system (XML, JSON...) where you could do a few things that are not practical with SQL.

<shop id="45u2y">
<items>
...
</item>
</shop>

<items>
<transform id="twoHanded"><dmg type="cut">3d6+2</dmg><slot id="hand"/><slot id="hand"/></transform>
<transform id="oneHanded"><dmg type="cut">2d6</dmg><slot id="hand"/></transform>
</item>
</item>


4 If I had to use SQL I would probably do something like that:

shopItems
shopItemId, shopItemStatus

items
itemId, itemName, itemCathegory

itemsTransformators
itemId, transformatorId, transformatorType, transformatorCondition


Etc... In this case I really believe SQL is not the best bet. I would definitely use a custom storage system, XML or if really needed another type of DB. ex: eXistDB http://exist.sourceforge.net/

• I can't comment elsewhere: Josh Petrie has a good answer but I would add something to ShopInventory: shopId, itemId + quantity. filling the table with 300 duplicates if you have 300 of a resource in a shop is also a violation of 1NF: simple-talk.com/content/print.aspx?article=712 but for prototyping always go with something more dynamic (like XML of Json) you will lose less time. – Coyote Oct 3 '11 at 16:32
• Yup, I agree about the duplication thing, I didn't want to get too deep into the complexities of a system I was advocating the OP not use anyhow, though. :D – user1430 Oct 3 '11 at 16:42