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?
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:
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.
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.
