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.
Worry about databases later.
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.
