Take the 2-minute tour ×
Game Development Stack Exchange is a question and answer site for professional and independent game developers. It's 100% free, no registration required.

In a game that I'm working on, any player has (at max) 460 items within their inventory. Each item has simple attributes, such as owner and quantity, but equips have different attributes, such as strength, dexterity, luck, intelligence, speed enhancement, jump enhancement, etc.. To save all the items, I have a column in a MySQL table for every item (meaning that equips also are categorized as items), and another table for extra equipment data.

The player has the ability to change the stats of any item, via obtaining more of a certain item, removing some of the items they have, scrolling an item to change its stats, and so on.

What I'm trying to figure out is - what's the best method to save all this information? Currently, what I do is delete all items that the player owns within the item table (which the equip table is linked to, so those entries get deleted too), and then insert the items that the player has. The issue with that is that there's a very high chance of players losing their items in the event of the server crashing, and it seems kinda odd to be deleting entries.

My new proposed method is to set a flag within each item's instance, and (for example), if the flag is 0, the item has not changed and does not need to be resaved, if it's 1 then some attribute has changed and needs resaving, and if it's 2, the item needs to be deleted from the database. I'm not sure if this is any better though.

Additionally, how often should I be saving the items? Right now it's done after important events (like quest completion), but what about saving on a timely basis?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

You should be committing the delete of items and the insert of the items in a single database transaction so if there is a crash there is no risk of items being lost. With a transaction its all or nothing!!

http://dev.mysql.com/doc/refman/5.0/en/commit.html

share|improve this answer
add comment

First, let's separate your data. A player table could look something like this:

Player
------------------
id      INTEGER PRIMARY KEY AUTOINCREMENT
name    VARCHAR(255)

While an item table could look like this:

Item
------------------
id      INTEGER PRIMARY KEY AUTOINCREMENT
name    VARCHAR(255)

Note that I haven't added anything fancy like effects or whatnot. This is the minimum information required to identify a certain name. Note also that every row in both tables has a guaranteed unique identifier.

Now we want to map certain stats to an item. We will in fact need two new tables. First the Stats table:

Stats
------------------
id      INTEGER PRIMARY KEY AUTOINCREMENT
name    VARCHAR(255)
value   INTEGER

Let's say we have a potion that generates health. First we'll add it to the Items table:

Item
------------------
id      name
0       Potion of Health

And then we'll add its stat to the Stats table:

Stats
------------------
id      name            value
0       Boost Health    100

So how do we link the Stats entry to the Items entry? We use a map table:

MapItemStats
------------------
id          INTEGER PRIMARY KEY AUTOINCREMENT
item_id     INTEGER
stats_id    INTEGER

And we'll add an entry:

MapItemStats
------------------
id      item_id     stats_id
0       0           0

Now we can map items to stats. First we'll need the items:

SELECT Item.id, Item.name 
FROM MapItemStats 
INNER JOIN Item 
ON MapItemStats.item_id = Item.id;

This query will give us the following result:

id      name                stats_id
--------------------------------------
0       Potion of Health    0

Not very interesting, but we can a more complicated query. First we'll save the result of this as a temporary table. Note that this is not valid SQL and purely for demonstration purposes.

(
    SELECT Item.id, Item.name, MapItemStats.stats_id 
    FROM MapItemStats 
    INNER JOIN Item 
    ON MapItemStats.item_id = Item.id
) AS ItemTemp

Now we can use this temporary table for additional look-ups:

SELECT ItemTemp.id, ItemTemp.name, Stats.name AS stat_name 
FROM (
    SELECT Item.id, Item.name, MapItemStats.stats_id 
    FROM MapItemStats 
    INNER JOIN Item 
    ON MapItemStats.item_id = Item.id
) AS ItemTemp 
INNER JOIN Stats 
ON ImageTemp.stats_id = Stats.id;

This will give us the following result:

id      name                stats_name
--------------------------------------
0       Potion of Health    Boost Health

Now, when we change any of the properties of an entry in the Stats table, none of the other tables are affected. Because everything is stored only once, it's very easy to delete references to an object. This is referred to as database normalization.

In your question, you talk about player's inventory. Well, we'll use the same trick:

MapPlayerItem
------------------
id          INTEGER PRIMARY KEY AUTOINCREMENT
player_id   INTEGER
item_id     INTEGER

So if you want to remove an item from a player's inventory, you simply look up the record in this table and delete it. For example, this would clear the inventory of a player:

DELETE 
FROM MapPlayerItem 
WHERE player = 0;

However, there is a major downside: queries become increasingly complex and expensive as you add more properties. Therefor it's often a good idea to selectively denormalize a database. For instance, if you just want amount of items a player has in her inventory, it's a bad idea to do the whole mapping thing I did above. Especially when you have a lot of records, this becomes very expensive.

So we'll change the Player table:

Player
------------------
id                      INTEGER PRIMARY KEY AUTOINCREMENT
name                    VARCHAR(255)
cache_inventory_count   INTEGER

This entry is not guaranteed to be 100% accurate, but that's okay. We only have to change it when an item is added to or deleted from a player's inventory.

share|improve this answer
add comment

I suggest to have 3 tables.

The first: Items with the columns:

itemId
itemName
itemDescription
itemPrice
... etc

The second table: Players with the columns:

playerId
playerName
playerEmail
... etc

And the third table: Inventory with the columns:

playerId
itemId
itemQty

All tables are linked to each other by playerId, itemId

Executing SQL query by Inventory table you can get a list of items for a particular player. Also via SQL update query you can update Inventory table for a particular player according to a particular item.

Commit changes to tables on game milestones, for instance when an achievement is unlocked, or level is passed.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.