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?
The same applies for the mystery_boxes and exp_ups tables (obviously they have different columns but the idea is the same)