I want to create an inventory system for an online multiplayer game, so I was thinking about database.

My research led me to this form:

  • id (user, bigint, unsigned + index, foreign key for user's id in users table)

  • itemid (item_id, int, unsigned)

  • amount (amount of the items, int)

I know there will be a lot of rows with duplicated user IDs - are there any improvements I can make to this design to work with that?

  • \$\begingroup\$ Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. \$\endgroup\$
    – Community Bot
    Aug 31, 2021 at 13:17
  • \$\begingroup\$ Done. Hopefully it's better now. \$\endgroup\$
    – Gamer
    Aug 31, 2021 at 13:21

2 Answers 2


Yes, this is a properly by-the-book normalized database design. It allows operations like checking the quantity of a specific item with a single database query returning just that one number and it allows analytic queries like getting statistics about how much of what item is in the game.

If you make use of those abilities, then that's fine.

However, in many multiplayer online games, there are only two database operations which ever happen on characters. And those are:

  1. Save the character completely
  2. Load the character completely

If that's your usage pattern, then database normalization into lots of rows in lots of tables gives you no benefit, but is a lot of work and a lot of additional performance cost. You can just as well serialize the whole data of the player to a BLOB and store that in a single field of a single table row.

If you still want analytics, then you can have a properly normalized copy of the player data in a separate data-warehouse database.

  • \$\begingroup\$ As a side note in some databases like Postgresql it's better to use jsonb. From an older question: gamedev.stackexchange.com/a/108566/15696 Postgresql has added a lot of json features. \$\endgroup\$
    – Sirisian
    Jan 28, 2022 at 19:32

A lot of rows is not a problem per se, even more so since you do not neet to ask for the whole inventory all the time - it should be enough to get the full inventory once at the start of the game and keep it locally at your player and just do another check on the server once the player actually uses an item (to prevent client side manipulation of item amount).

Before you encounter an actual bottleneck (which is unlikely to be the database), your approach seems to be ok.


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .