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.
