MySQL - Best method to saving and loading items.

Question

Hello,

So on my older work, I had always used the 'text' data type to store items, like so:

0=4151:54;1=995:5000;2=521:1;

So basically: slot=item:amount;

I've been looking into finding the best ways of storing information in a sql database, and everywhere i go, it says that using text is a big performance hit.

I was thinking of doing something else, like having a table with the following columns:

id, owner_id, slot_id, item_id, amount

Where as now i can just insert a row for each item a character allocates. But i have no clue how to save them, since the slot's item can change, etc. A character has 28 inventory slots, and 500 bank slots, should i insert them all at registration? or is there a smarter way to save the items

Answer 1

Yes use that structure. Using text to store relational data defeats the purpose of a relational database.

I don't see what you mean by insert them all at registration. Can you not insert them as you need to?

Edit

Based on your previous comment I would recommend only inserting a slot as it is needed (if I understand your problem). It may be an idea to keep the ID of the slot in the application, if need be.

Answer 2

If I understand you correctly, and that the slot's item can change, then you want to further abstract the mapping between item_id and the item:

entry_tbl.item_id->item_rel_realitems_tbl.real_id->items_tbl

This way, all entries with an itemid point to a table that maps those ids to a mutable item. When you UPDATE an item in 'items_tbl' then the mapping automatically updates the entry_tbl.

Another JOIN is needed however. I would also use stored procedures in any case to abstract the mechanism from semantics.

I am not sure I understand the wording of your question however.