I'm making a game and in that game there are different types of items (weapons, armor, potions, etc.) each type has different statistics that need to be stored in a database.

I was thinking of making a "weapons" table, an "armor" table and etc. but struggle to think of a good way to relate them all.

To display a list of all items to the player it would be easy to just hardcode a select query of every table and aggregate somehow in the code afterwards, but what design patterns / options are there for storing what items the player would have in his inventory.

The only thing I could think of is to have a table which would hold an ID which would correlate to an item type / table. In that way I could store a player inventory's data in the database as key value pairs, for example ( "24, 3", "54, 1" ) which, let's say for the sake of the example would translate as item with ID "24" in table with ID "3", which would be "potions", etc.

However I feel like this can be improved and is ineffective.

I plan to use EF Core for the game.

So, let's say I have 3 item types: weapons, armor and potions. They have the following properties:

weapon - weight, cost, damage, damageType, minStr
armor - weight, cost, armorPoints, bodySlot, hardness
potion - cost, type, effect, effectAmount

CodePudding user response：

Having different tables for different types of inventory items is not a bad idea. Using a magic number (your e.g.: 3=potions) is probably not a great idea.

As you've already realized, the advantage to having different types of inventory tables (weapons, armour, potions, food, ...) is that these different types of things have different properties. Each inventory type table will have columns for the properties that are appropriate.

The real question is how do you keep track of what's in the player's inventory?

Most games have a certain number of slots for different types of items, so having a single "everything in the player's inventory" table probably isn't directly helpful.

You can have one table for the weapons, one table for the armour, one table for the potions, and so forth. You probably want this table to include a key pointing to the type of thing ("Gauntlets of Punchiness") and one column indicating which slot the item is in. Remember, tables aren't like arrays, they don't have a natural order you can sort by or use as an indexer even though things are stored physically in order. This table might also include a quantity column, if that makes sense. You might have 10 loaves of bread in your food table that only take up one inventory slot, for example.

Combined Listing

If you did have a need to produce some kind of combined list, you can use a view (which is just a persisted query) that does a UNION operation over some consistent set of columns. This involves writing a query against each of your inventory tables joined to their respective item type tables. The restriction here is that you need to have the same number of columns in each part and they need to have consistent data types or the UNION won't work.