Best way to store inventory in a database : each item instance gets its listing, or each player has an "inventory" field?

Question

I'm making a browser MMO using PHP and MySQL. I'm hesitating between two ways to implement player inventories :

1. when crafted/dropped from a monster/found, an item creates a new row in the Items table, storing its type (instance of a dagger, of a potion...), its location (on the ground at tile X:Y ? in player Z's inventory ?) and maybe an additional field or two. An item's listing would look like this :
   id=728 ; type=14 ; location="i426" ; special="e8"
   (where "i426" would mean "in the inventory of player n°426", and "e8" would mean the item has the enchantment n°8).
   Pros : I can store data about each individual item - enchantments, durability, previous owner... Also, the items are easily placed in any location; putting the items in, say, auction houses, comes for free with this system - not to mention crazy possibilities such as items inside items, etc.
   Cons : with hundreds of players looting monsters and crafting all day, this quickly creates a huge table, and it seems pretty wasteful. I fear this might slow down the whole game (I'm using a lot of AJAX voodoo) anytime items are involved.

2. or : when a player puts an item in their inventory, that player's inventory field on the database is updated with the new item's type (an integer) being appended at the end, with a delimiter. A typical inventory field would look like this : "|11|8|27|58|58"
   Pros : much less wasteful. An item instance is just a few digits and a delimiter.
   Cons : no extra data on items; every instance is the same. Also, dropping an item on a tile means that tile needs its own inventory field - and so on for every possible location of an item. Besides, this method involves juggling with strings anytime an item has to be added/removed, rather than simply deleting a row from the table. Another thing is that a player's inventory can store a variable number of items, so this string has no predictable length.

In a previous project, I used the second method. The first method looks fun, but I'm not sure if it'll agree with our storage space and our server's speed. Which of those methods is most often used in MMOs ? Is it another one altogether ? Are there some pros or cons I've missed ? (I'm sorry if this question is a bit subjective !)

Chemesh

I'm guessing almost all systems use something more similar to type 1, since type 2 is an antipattern named "Jaywalking". It stems from the fear of intersections. – Tangential

Is a player's inventory limited? Do they have say, 10 slots available? Or is it limited by weight or anything like that? – Lys

@MarcusAdams - darn ! And I thought I had found something clever. – Chemesh

@MichaelFredrickson - there are limits, but they aren't set. They can change from skills, bags, etc. – Chemesh

I would not be overly concerned with storage throughput at this stage of development. Be wary of early optimizing. Assuming you're anticipating a high volume of transactions in a short period of time, you likely will be performing most of these transactions in a memory cache which will be flushed to the database as needed to not block player UI requests. – Cryptogram

Answer 1

Your best design is a version of approach 1: every item has its own row and you put all the necessary information there. Don't worry about optimization yet - you'll have time for that later when you see how users use your system and where the bottlenecks are.

For now, you want to design for ease of programming and clean, unambiguous descriptions of state. You want a master items table for each "type" of item, where you keep information about what that item is and how it can be used. Then, when created (or instantiated), you put a row in the items table and describe the specifics of that instance of the item. When it moves, you can update the location information. It isn't wasteful, because you are storing -- once and in the right place -- only the information you need to manage the item.

By putting the right indexes on the table, you'll be able to manage millions of them easily and quickly.

Ipsambul

Well it's decided then, that's what I'll be using ! – Chemesh