Geek Answers Handbook

Inventory database design

I'm new to using MySQL, and I'm trying to figure out what is the most efficient way to store player inventory items in my database.

So here is the setting:

Here is a table called 'player' , and each player is assigned a unique 'playerid' , which is set as the main index of the table.

Each player can have up to 24 items in his inventory, and information about these items is stored in a table called "player_inventory" . This table has the following fields:

playerid, slid, uid, stack, uses, durability

'uid' is the identifier of the element, and 'stack' , 'uses ' durability - these are just the values ​​that each element needs (for example, the same type of element in another slot may have lower "durability" ).

The problem is that I cannot set the index to 'playerid' in the inventory table, since there are up to 24 entries in slots per player, and none of the other fields will be unique.

So, I'm worried when this table contains stocks of 10,000 players, this table could potentially have 240,000 records without an index, when I go to the query - is something telling me that can be very slow?

I have fairly limited knowledge on how to optimize my database, any advice is very welcome.

+4
source share
3 answers

Indexes, including a unique index for the primary key, can be defined for multiple columns.

ALTER TABLE player_inventory ADD PRIMARY KEY (playerid, slotid);

This means that the combination of values ​​in these two columns must be unique. But this player can appear on several lines, and this slot can occur on several lines.

+3
source

It might be a good idea to have 3 tables.

  • tblPlayer (idPlayer INT PK NOT NULL AUTO_INCREMENT, username, password, etc.)
  • tblItemInventory (idItemInventory INT PK NOT NULL AUTO_INCREMENT, idPlayer FK, idItem FK, ..)
  • tblItem (idItem INT PK NOT NULL AUTO_INCREMENT, Durability, Uses,)

tblItem has up to 24 (idItem = 24)

+1
source

There are two lines of thought about setting an index for a table, such as your inventory. One of them involves the presence of a surrogate key, mainly an automatic increment index for a table that is not related to your application data. It serves only as an index.

Another way you can provide an index is to use a combination of playerid and uid. Although if a player can have more than one stack of the same element (for example, in Diabo or in MMO), you can probably use a combination of playerid and slotid as an index. You may have a composite key consisting of several keys in MySQL.

0
source

More articles:


All Articles