Character Inventory

[verycleanteeth]

This is a database structure question. I've got a game where you have a character. This character has an inventory. Right now there are only about 100 items this character can acquire, but this number will only increase.

 

I currently have an inventory table with a unique Character_ID column, and then 100 columns, one for each item. It looks something like this:

 

Character_ID | i1 | i2 | i3 | i4 | i5 | i6 |..........etc.

94              |0  |5  |2  |99 |1  | 0 |...........etc.

 

 

here character 94 has 99 of item i3 and 0 of item i1.

 

Okay. I know this isn't the proper way to set up an inventory. If I one day have 1000 unique items, I don't want a table with 1000 columns.

 

What is the best way to set this up? I could have another table that looks something like this:

Character_ID | item | quantity

94              | 2    | 5

94              | 3    | 2

94              | 4    | 99

94              | 5    | 1

 

but that seems like it would also get unwieldly quickly. Without an index I'll be doing queries like "SELECT * FROM inventory_table WHERE Character_ID = '94'". If I have 10,000 users with 1000 items each that's 10,000,000 cells of data. These queries are going to be slow.

 

So, yeah, any suggestions?

[btherl]

The structure you're proposing is the right one (or at least the right starting point).  Definitely ditch the columns, that will drive you crazy later

 

Can characters really hold 1000 unique items each?  At one time?  That's a lot of pockets   You might notice that most games impose a limit on the number of items that can be held, either weight or size based, or a fixed number.  Part of the reason for that is efficiency.

 

Access can still be quite fast.  How often do you need to list EVERY item a character has?  If you need to test for a given item, then an index on the character id and item column will give you instant access to that knowledge.  The same index can be used partially (on the character id column only) to fetch all items belonging to a character.

 

If you're really worried, setup the table and setup 10k dummy characters, each who hold 1 of each of 1000 items.  Then do a few queries and see what happens.  I imagine you'll have no trouble unless your players are compulsively checking their inventory every few seconds, AND they all happen to have their pockets stuffed full with 1000 unique items.

[verycleanteeth]

Good to know I'm on the right track. I'll actually be doing quite a bit of listing the entire inventory. People like seeing their entire heaping collection in one, well, giant heap. And inventory limits are no fun!

 

It's gonna be fun restructuring my code to take advantage of the new table structure! Thanks for the reply.