[php/mySQL] Inventory

[Perry]

I am working on my inventory system.

My idea was to put 3 fields in my inventory table. id, name, and class. id would be the player's id. name would be the items name. class would be the type of item. I would then make a table for each different type of item. Mineral table for things received from mining etc. Then when I needed to display the inventory I would select all with the id that is equal to the id of the player. Then to display the information I would select the items with the names from the different tables based on the class field. Does this make sense? Does anyone see a major flaw this system would have? Thanks for any input and if you need clarification just let me know. Thanks again.

[62896dude]

This sounds very achievable, good thinking!

[Jackolantern]

If I am following you correctly, you are talking about having a master item table or tables, and those will only be represented as belonging to the player by having an id for the player combined with an id for the item, right? If so, then that is how you do it

[Perry]

I'm not sure if that is what I mean. I will make a basic table, minerals for example. It will have fields like name, modifier, value, level, etc. I will make all the items in the table manually. Then whenever someone goes to mine something one of them will be randomly selected and placed into the inventory table with the players id, the items name and the type of item. Then I will be able to display all of the other information about the item by using its name and type to do a query. Is that what you mean jack?

[Jackolantern]

There is no reason to store the item name or any other item info in the player inventory table unless any of it is created dynamically and is unique to that player's item. In fact, you could probably get away with having only 3 fields in the inventory table: player id, master item id, and the date and time the player gained the item. You could also choose to have a quantity field instead of repeating the same info for item multiples, which likely would be the way to go if you have high-stacking items like arrows or bullets. Also, if you have several different master item tables (which you probably would since armor would have many more field requirements than a crafting material), you would need to have some kind of identifier in the inventory table that tells you which master item table the item id references.

The bottom line is that you don't want to needlessly repeat data in the database, because that sets you up to have "data anomalies" where you have conflicting information in different places, which can be catastrophic to the health of your data.

[Perry]

My reason for storing the name in the table was basically for the same reason as the master item id, but now that you mention it, it makes more sense to give every item a number instead of going by name because some things could have the same name. Thanks for the advice. I will more than likely end up back here if I run into problems.

[Jackolantern]

Indexed numbers are also orders of magnitude faster to search for then strings

[Perry]

I spent some time on it this morning and got it set up and working. Thanks for all your advice. I am proud of how it turned out. Even though it probably isn't the best yet, it is the first thing I really did on my own in php without following how halls did it very closely. Thanks again

[Jackolantern]

You're most welcome