0
\$\begingroup\$

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:

enter image description here

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: enter image description here

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

Improve this question
\$\endgroup\$
4
  • 1
    \$\begingroup\$ For a market place, you could make a new table that contains its own id, the player id and the weapon id, time listed, initial price, current price, etc \$\endgroup\$
    – Zibelas
    Feb 22, 2022 at 15:28
  • \$\begingroup\$ @Zibelas Yes however I would have to then make 3 tables (because I would also need to make a marketplace for exp ups and mystery boxes) because I have 3 different entities correct? I'm not sure if this is scalable friendly because everytime I introduce a new entity I'd have to create 3 tables (the entity itself, the user_.. table to store who has what and the marketplace for that entity) isn't there a simpler way? \$\endgroup\$
    – sharpness
    Feb 22, 2022 at 15:29
  • \$\begingroup\$ I would not even have a weapon table. One table for items and one column would be item type that defines the item. So you would still have a weapon table, but the user would refer to the item table of it. Your user weapon table would be changed to something like user item table and the weapon id to just the item id. Regardless if it is armor, weapon or tool, they would all fit. A mystery box is just an item, you would have a different table for just mystery box that specifies what kind of items it could contain \$\endgroup\$
    – Zibelas
    Feb 22, 2022 at 15:40
  • \$\begingroup\$ @Zibelas Thanks alot, could you perhaps give a visualization of what you mean? That would help me understand it better \$\endgroup\$
    – sharpness
    Feb 22, 2022 at 16:12

1 Answer 1

Reset to default
1
\$\begingroup\$

Example data model

enter image description here

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.

Improve this answer
\$\endgroup\$
3
  • \$\begingroup\$ Thanks a lot, I had a feeling you meant this and this design works good however there is one crucial thing missing here. How would I add other attributes that are specific to a copy of an item? I see you added the durability column there but this does not apply to a potion for example so it would have a NULL field is this an issue? If potions have 5 additional columns, it would leave weapons with 5 columns on NULL because they dont have this attribute. And in the example I showed it would eliminate this since all user_items have their own table which specifies the attributes for that item only \$\endgroup\$
    – sharpness
    Feb 22, 2022 at 19:29
  • \$\begingroup\$ The inventory is only a collection of the state of the item. It depends on your type of game, what other states your items could have. The potion could have as well a durability of 100 or 1, since there is no logic attached to it. Your game code would handle how to reduce it. The advantage of this system is, you could introduce new items or types without the need to adjust old tables. To model it more accurately to your game, you would need to provide further information, in the end it is your game \$\endgroup\$
    – Zibelas
    Feb 22, 2022 at 19:35
  • \$\begingroup\$ What other information do you need, I'd like to provide you with more information but I'm not sure what to further provide. I like your solution and I have been thinking of it but I also want a sort of normalized design (what I shown in my post). When using the solution you provide I'll end up with lots of columns where NULL values are allowed and I'm not sure if this is a good idea. Is there not a way to have it all normalized and split up into tables? \$\endgroup\$
    – sharpness
    Feb 24, 2022 at 15:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .