MySQL/PHP Game Inventory

[MrFish]

I need some ideas and advice on how to do something like this. Currently I'm just listing items. But I think a multidimensional inventory would be a lot more user friendly. I just don't know how I should do it. I can think of different methods but I need a scalable and efficient one. Here is a visual of what I'm trying to achieve-I'm stumped on the best way to do it. I've had many ideas but they all seem to fail somewhere. Here are the requirements-1. Each user should be able to acquire an infinite amount of inventory space. Take the above image for example. Lets call this set of 20 slots a "bag". Users should be able to acquire many bags if they want. Even order and rename them for organization.2. Users should be able to arrange items in any slot in the bag.3. Bags, if deactivated, will hold their contents but not be accessible by the user until they reacquire it.4. Each slot can hold more then one of the same item (like seen above)5. Each slot should hold attributes for that item. For example, hold the weight value of a stack of arrows.Is it simpler then I think it is. I don't see any good way to do this.

[Ingolme]

It sounds like you'll need a database system with several tables:

• A table for users
• A table for bags
• A table for items
• A table relating items with bags
• A table relating bags with users

You'll have to figure out the rest.

[MrFish]

So you're saying I should record every item that exists and its quantity? Will that toll the system?And for bags, have 20 fields representing each slot or do something different?

[thescientist]

I would make a record for each type of item and all the possible properties it could have. At runtime, a player who has a particular item would instantiate that item with the appropriate values that make that users instance of that item unique. i.e. a blue pouch vs. a brown pouch.For the bags, as long as the number is finite and would be the same for every instance of that item across the board, then you could implement it with that kind of design.

[Ingolme]

So you're saying I should record every item that exists and its quantity? Will that toll the system?And for bags, have 20 fields representing each slot or do something different?

You don't need to record the quantity. You get the quantity by counting how many of the particular item is associated to the bag, or user.The bags don't need 20 fields, they just need a field that identifies them, then you'll have a table that relates items with bags. It's up to you to make sure that no more than 20 different types of items are associated to each bag. In my university there's a subject called Database Theory that teaches how to organize things in a database.

[justsomeguy]

It's probably more efficient to have a field for quantity instead of 1 record for every arrow or coin. The bag table could have a field that says how many items it carries, and the table for items in bags would list the item ID, bag ID, sort position in the bag, and quantity. It's up to you to figure out which fields you want in the tables though, i.e. what information you want to keep track of about the different pieces. The general rule about relationships in databases is that for a one-to-one relationship, where assuming the tables are bags and items, a one-to-one relationship means that one bag can have exactly one item, and one item applies to exactly one bag. With a one-to-one relationship, you only need 2 tables, and each table can have a field to hold the ID of the record in the other table that it relates to. For a mnay-to-many relationship, where one bag can have many items, and each item can be in many bags, then you need the same 2 tables for the items and bags, but instead of a single field to relate them you need a third table which holds the IDs of the two related records. There is also a one-to-many relationship, where you still need a third table.