Identifying unique items in an inventory system?

[Luni]

So I have a quandary.

I'm building an inventory system with support for common or unique items; I'm still wrestling with the problem of how to identify them in the database.

[b]1)[/b] One single items table, with an auto-incrementing UID to identify each one, and fields to identify what character or container it exists in.
[b]Pros:[/b] Every single item ever created has a single number to reference it. Can easily differentiate between two "identical" items (different UIDs). Can easily log everywhere an item's been.
[b]Cons:[/b] Every single item ever created has a single number to reference it, including common items that might only exist for a day. Duplicates can exist; item IDs will eventually run into the hundreds of thousands.

[b]2)[/b] Two tables -- one to store types of items, identified by serializing their data fields and CRCing it (so that generic items and unique items that have been customized similarly will resolve to the same IDs), and another to link item CRCs to character IDs.
[b]Pros:[/b] Seems more elegant, will never get into absurdly-high item IDs if the server runs for a very long time. Very low chance of duplicate items.
[b]Cons:[/b] Possible CRC collisions. Queries may be less efficient; don't know if SQLite likes JOINs very much. Have to identify instances in an inventory by x,y coords or in the world by entindex.

I'm kind of enamored with the second approach but I don't know how inefficient it may be to do CRCs when saving items, which approach has more efficient SQL queries, how common CRC collisions are... and so on.

Is using CRCs as unique IDs even worth pursuing, or am I an idiot for worrying about item IDs reaching enormous numbers in the very long term?

[Divran]

2, but why do you need to use crc?

[Luni]

To get a numeric index that'll be the same for every item with the same data fields.

Ideally, every type of item would have its own item:GenerateID() function that would handle what fields to save (an ID badge that saves its owner's information, a walkie-talkie that saves its frequency, etc).

[Bletotum]

What about a table of item bases, and another table listing base entries, modifiers, and place of storage(players)?

[Kogitsune]

Could just use a byte of data to store the item grade - common, uncommon, rare, super rare, unique.

So, checking unique would be like this:

function item:IsUnique( )
  return self.Grade == ITEM_UNIQUE
end

[Bletotum]

You're missing the point, which is to have customized entities (for example, those in torchlight 2, or enchanted items in minecraft), and how to organize this information.

[initrd.gz]

[QUOTE=Bletotum;37880561]What about a table of item bases, and another table listing base entries, modifiers, and place of storage(players)?[/QUOTE]

Yea this is a much better solution; I'm using it in my game and it works extremely well. [url=http://sqlite.org/foreignkeys.html]Foreign keys[/url] also help with consistency and moving rows around.

[_nonSENSE]

How I handle items with custom attributes in my addon:

Each item has a base. This base item holds several properties that are set in stone for that base item, such as:
- ID
- Name
- Description
- Cost
- Type
...

I actually store all of these base items in a shared Lua file. Since these bases only change when there is an update, it's a lot easier to just send them to the client once instead of networking them. You could just as easily store them in a table though.

Any other properties that the item has (if any) are stored in a table along with a reference to the item base. The final item is stored as a json-encoded string, but that is just my own preference. Here is what an example row in the table looks like:

(steamid | inventory-place | item-code)
STEAM_0:0:1 | 1 | {"i":"staoha","t":0,"u":3,"p":4}

The item base (i) is "staoha" which refers to the item "Stack of Hats". The item is not tradable (t = 0, default = 1), it is craftable (default = 1, omitted), it has an effect (u = 3, default = 0, references a particle effect in a table) and it has a paint applied (p = 4, default = 0, again references a paint code in a table).

When joining the game, the server loads all the items from the database an decodes them using the JSON library. It then references the base item table and adds any custom properties. Since we do not create a new Lua table for each item we load, we can also save a load of memory (no table.Copy).

Hope this helps.

[Luni]

Hey, thanks for the responses. I realized saying "common/unique" was a bit misleading, but they've been food for thought.

Item bases are definitely going to be hardcoded; the client will have to know, for example, how to build a context menu for the item, and sending strings to RunString from server to client is.. eurgh.

The data they have to store is a lot more than just the grade of the item; each class of item — weapons, clothing, devices — will store different kinds of data, which necessitates every item class having its own save/load functions to tell the script what to store in the DB. And, of course, it finally occurred to me that without unique IDs, the items-as-containers scheme I was hoping to do falls to bits.

It's gradually occurred to me that while using CRCs and shit is "cool," which is clearly the best reason to anything ever, it's... turning out to be not so reliable, and my highest priority is keeping the system stable and reliable.

Unique IDs ho!