0
\$\begingroup\$

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:

enter image description here

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.

\$\endgroup\$
3
  • 2
    \$\begingroup\$ In essence, what your asking is "here is this data table I wrote in c++. Given this access, what is more efficient". This would be a much better question for stack exchange. Apart from the end-level of using this in a game, this doesn't really specifically relate to game development. \$\endgroup\$ – Gnemlock Sep 18 '16 at 23:55
  • 1
    \$\begingroup\$ I think this is fairly relevant to gamedev; online and social games are becoming more and more common, and a lot of the database specifics of managing a large MMO derive a lot from game-specific usage patterns. That said, I'm not sure this question has enough information to answer properly. e.g., what sharding approach is in use, what CCU, what is the frequency of writes vs reads, what relations need to be enforced on items, etc. \$\endgroup\$ – Sean Middleditch Sep 19 '16 at 0:22
  • 3
    \$\begingroup\$ Are answers from game developers going to be different / better than answers from database developers? Does the game dev context change the nature of the question? \$\endgroup\$ – Pikalek Sep 19 '16 at 1:03
3
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.