Inventory Table efficiency

Question

I'm building an inventory system for a MMORPG game that can possibly contain massive numbers of players (~20.000). Is it smart to make an InventoryTable where each row contains only one item, or should I make one big VarChar column that contains all of the information about all Items for a player.

A player has about 200 items.

And every item has about 20 additional pieces of information: ItemID, Amount, ...

So this big varchar column can be something like 15000 chars long.

Here are some basic attributes:

What will be faster?
- Reading maybe 200 lines from a big table for each player
- Or one big line for every player and serialize it in my server.

Is use a MSSQL database with ODBC.

Answer 1

If you want to do relational database design by the book, you would use the first solution. Encoding multiple independent values into the same column by encoding them in a string is a big no-no.

However, in the context of a game your usual use-case is to save or restore a player completely. Putting all the info into fewer tables and rows will improve performance.

But there is a price you pay for that performance improvement. No, not just that the ghost of Edgar F. Codd will haunt you and write bugs into your code while you sleep. You will lose the ability to effectively analyze and modify the inventory data outside of the game with simple SQL queries. For example, these are some SQL operations you might want to do one day which only work well with the first solution:

• Create a report of how many items of what kind are currently in the game (SELECT ItemId, SUM(Amount) FROM Inventory GROUP BY ItemId)
• Remove an item from the game completely (DELETE FROM Inventory WHERE ItemId = 42)
• Give a free item to a player (INSERT INTO Inventory VALUES( 13, 42, 1))
• Give a free item to a subset of players (INSERT INTO Inventory SELECT PlayerId, 42, 1 FROM Players WHERE RegistrationDate < '2016-01-01 00:00:00');

But when you don't think you will ever need to do any of these or are comfortable with building a more expensive workaround for them, sure, go ahead.