Database Question

[Emicarn]

I'm not sure this is the right place to put this, if not please feel free to move it!

I'm working on a new, less graphic, project.  What I'm not sure about is how to handle inventory.

The potential right now is to have upwards of 300 unique items, which a player can gather in quantity of up to 10 for the sake of this question.   What I'm not quite sure the best way to handle this tracking. 

Each time will contain:


item - name
item - number in inventory
item - quality [this is up in the air but may be 6 levels of quality]

Would it be better to add an line to an inventory table with the player ID, item name, item quantity, and quantity they have in a single large table.

Or create a seperate table for each player?

This has the potential to become unwieldly and slow if a player base grows.

300 items x 6 levels of quality = 1800 variations x number of players

This may seem clear as mud, if so I can try to make it clearer, just looking for some guidance or suggestions.

Thanks!

Rich

[Chris]

2 tables. One [SHARED] that holds definition (name, parameters) and another [INDIVIDUAL] that holds ownership (playerID, quantity and equipped status).

[Harkins]

Your tables are:

Player: id, username
Item: id, name, quality
Inventory: player_id, item_id, quantity

Do not make a player per table or item.

[133794m3r]

Your tables are:

Player: id, username
Item: id, name, quality
Inventory: player_id, item_id, quantity

Do not make a player per table or item.

Also the inventory you'll need another index. Since players will most likely have more than one item in their inventory. So create an id for it, since if you don't the database engine is likely to create it's own and it'll take up six bytes, and that index won't be able to be used by you.

The other thing is, only put an index on player_id and the inventory_guid(column made up in thin air) because any other column being indexed is pretty stupid.  Beyond that it seems to be ok. If you're using innodb i'd also do a foreign key on item_id, and player_id in the inventory table. But if you're not, it's not officially needed.

Also since the limit of each item is only 10, i'd use a tinyint for the quantity. The rest are likely to just be ints since you're going to maybe get up to that point in the future.

And if you're going to have them 'equipped', i'd have inside of the player table entries for the total equipped slots, and have the item placed there and it being seen as it's own 'inventory' like thing. Or you could make it hold it via that guid value.

Anyway the solutions posted thus far do seem to be some of the best ones for you to use. Also remember to use your indexes sparingly but not too little since selects could get slow.


Also my table for the inventory is this.(probably not applicable to everyone but this is my own.)

Code: [Select]

`players_inventory` (
  `inventory_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL,
  `player_id` int(10) unsigned NOT NULL,
  `amount` smallint(5) unsigned NOT NULL,
  `item_slot` smallint(5) unsigned NOT NULL,
  `item_durability` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`inventory_id`),
  KEY `player_id` (`player_id`)
) ENGINE=InnoDB

And the players table is filled with things like their actual 'equipped' status. If you're going to have variable amounts for it, as in you don't know how many they'll be able to equip. It's probably best to just add a simple tinyint column called 'equipped' to the inventory table.

Beyond that, I don't know of anything else to add to this discussion at all, right now.