0

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

mummy
  • 49
  • 5
  • @KenWhite, if I had one table with a column for item type, how would I record the different stats that some items have and others don't? – mummy Oct 15 '21 at 19:55
  • Yes, those are called `NULL`able columns, @mummy . – Thom A Oct 15 '21 at 19:57
  • @KenWhite, I will be translating it in code and fetching the data a second time. – mummy Oct 15 '21 at 20:02
  • @Larnu, is having a table where 80% of the values are null good design? Because for every type of item all the columns which would hold values for another item type would be null and they would be way more than the columns which will be filled for any item type. Also I would need to have an EF model for the table and a separate model for each item type and translate those in code. – mummy Oct 15 '21 at 20:04
  • @KenWhite, if it isn't abundantly clear the whole point of this is to get information on better ways to do it. So far you haven't helped much beyond "That's a bad idea" (which I've noted in the question I also think) and "Make another table with a column" for which I asked for clarification. If you have some actual advice, please share it, but just saying it's a bad idea isn't helping much. – mummy Oct 15 '21 at 20:09
  • @mummy Similarly, you have provided very little concrete information. We can't generalise a solution for you, because we don't know the model you're working towards. Perhaps searching the Web on how to model inheritance in a relational database will help you? There are Many options just for that, which is best for you depends totally on information you have not provided. You current question is so abstract and broad in scope that I could write an entire book covering the possibilities. That's not what SO is for. Narrow your question down to explicit and concrete examples. – MatBailie Oct 15 '21 at 20:14
  • https://stackoverflow.com/help/how-to-ask – MatBailie Oct 15 '21 at 20:16
  • If 80% of the columns are `NULL` then 1. it sounds like you haven't given us the full picture. 2. You are being overly specific with your column names (i.e. `WeaponName` and `ArmourName` when you should have a column `Name`) 3. You have too many columns. – Thom A Oct 15 '21 at 20:44
  • @MatBailie, in my personal experience people who really know their stuff can answer even really broad and question and ask for details, if need be. While people who only pretend to be versed would do anything to beat around the bush BUT answer the question. In short, if I were to ask "How to get from A to B by car" someone of above average intelligence would just tell me which highway to take, someone else would ask asinine details like "what car model is it", "how many miles does it have on it" and etc. I see someone already answered my *abstract* question. Mind-blowing, isn't it. – mummy Oct 15 '21 at 20:51
  • @Larnu, yes, because weapons and potion items, naturally, would share 90% of their properties. I, personally, fight with a flask in my hand. – mummy Oct 15 '21 at 20:52
  • Yes, they probably do share most properties, @mummy . ID, Name, sell value, buy value, description, weight, durability, Effect Value (heal value for the potion, damage value for the weapon). Well done, you picked one of the few that don't match, equipment slot, and made it the 80% of the table. – Thom A Oct 15 '21 at 23:17

1 Answers1

0

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.

Joel Brown
  • 14,123
  • 4
  • 52
  • 64