Storing and saving player backpack(inventory) in database

Question

Is there best way to store data like player backpack in relational database?

Let's say, that I want to store player backpack in database.

Database model of table "eq" looks like:

playerId | slot | itemId | amount

Now let's say, that I have this row in database:

1 | 10 | 20 | 99

This mean, that player with Id 1, has 99 items of id 20, on 10th slot. It's easy to read those data to game(if row with slot Y doesn't exists, it means that's empty), but it's bit tricky to save those data. Why? Because if player use 99 items of id 20, his backpack will be empty in RAM, but there still will be row in database. Same when player will move the item on another slot, how to know when delete the slot in database?

Here is two of my ideas, which look bad:

1. When the player register new account, I can add X empty rows to "eq" table, with his id and following slots(1, 2, 3, 4...), then on save just iterate over player eq and update every row that belongs to player

2. When the save procedure begins, delete all "eq" rows that belongs to player, then insert to database only this rows, where are items(leave empty slots)

Answer 1

If your backpacks are a fixed size or rarely change, and not too big, there may be benefit in creating rows for all the slots during registration. I think that would mean indexes only get updated when players are added or removed, and the statistics used by the optimizer should remain stable and effective. It may be a good idea to use INSERT ... ON DUPLICATE KEY UPDATE ... queries on such a table to ensure it's self-repairing in case of accidentally deleted rows. It shouldn't be any less efficient than doing just an UPDATE.

You should also be able to do individual slot updates. Whenever a player uses an item from the backpack, UPDATE that slot's contents. If a new item is added, send an INSERT command. If the last item in the slot was used, send a DELETE command instead. When items are moved, you can just UPDATE the row to change the slot number. You should have enough context in memory to determine what to do, and only one player can be logged into an account at any time, right? If you pre-create the slots, you would just do suitable UPDATE commands instead of INSERT / DELETE commands.

If for some reason you can't update individual slots, you can save the whole backpack in two queries. I would send an INSERT ... ON DUPLICATE KEY UPDATE ... command with values for each slot that contains items. Then I would send a DELETE FROM eq WHERE playerId = ... query and if the backpack isn't empty, append AND slot NOT IN (...) with the list of populated slots. This way, you don't delete and create unnecessary rows, which should be more efficient. With pre-created slots, you can do it in one query.