proper items db design mysql

Question

I'm working on a game using MySQL as the db. I'm struggling a bit on the database design, this is the part I'm struggling with:

So basically exp_ups, mystery_boxes and weapons are entities in the game which have their own table to store information about a specific entity. The user_... tables contain which item what user has, copy of items can be owned by multiple people. So user 1 and 2 can both have weapon with id 1 but its stored in 2 rows. In user_weapons (& user_mystery_boxes & user_exp_ups) additional metadata about that specific copy can be added like time_acquired and other metadata.

The thing is here, how would I implement a marketplace where users can list their items? Or fetch all the items a specific user has (inventory), this is kinda hard because all items are in their own table with their own incremental id. Ideally I would want to have some sort of item_id which references to an item but a foreign key can only point to one table. Does anyone have an idea?

Sample data to clarify:

The same applies for the mystery_boxes and exp_ups tables (obviously they have different columns but the idea is the same)

Answer 1

Example data model

The idea is, you have a generic item with an item type. For each item type you define an extra table, that gives the attributes to the item you want (mainly weapons, armor). The auction table is now straight forward, just refer to the item id and the other columns can be relevant how your auction works (add start time, end time, current winner, buyout price, etc).

The mystery table is just a collection of item ids to the mystery item that describes the chance to get that item. Once the user would use the item, you would look up which box type he used, load all items corresponding to that one and roll accordingly which item he got.

If you are really against the null value, you could have a helper table that stores the modifications that are done to an item, be it temporary or permanent. Value is either directly like in case of durability or it is the id of the effect. I didn't add an enhancement table, but it could be something temporary like whetstones or weapon oils. With the new table, you can have as many effects on an item as you want. The id in the item_modification table is the id of the item in your inventory.

Keep in mind the performance of your queries. If you save performance by having something like durability (which will change often) in a table with quick access, there is a real benefit compared of not just having a few null values for items that do not need it. Depending on your player base, the number could grow quickly.