# database structure for rpg items

I am currently developing a multiplayer html5 game, and the game has rpg elements (levels, items and such)

I just started working on the database side of things, and now I have a mongodb server setup with a basic user table (email, nickname, password hash and salt etc)

Now, each user should aquire items, and I am wondering should each user get his own collection in the db, or should there be a single collection that stores all the items, and has a email field that links the item to a user?

One may miss a very important detail: you're using Mongo. It's not an RDBMS, it's a document-oriented DB and it should be used differently.

Namely, where a relational DB would use a foreign key (a reference, in fact) and rely on joins with indexes, you can embed entries right into your users' documents, RDBMS are terrible at doing so. A tradeoff, yeah. I personally think it's a better choice, read on and see if you agree.

Of course, that implies that once copied from the game-wide colection, the player's item is no longer linked to its "prototype item". Thus, should the prototype change, changes to it won't propagate to its copies (unless you explicitly spend time doing that). That, in turn, makes each copy self-contained. You mentioned upgrading certain items -- piece of cake, just increment a field on an object in player's inventory and there you have it. All other items like this one are not affected.

By using SQL-style data structure (a table with all items, a join table items-players) you'd end up with a ridiculous amount of lookups on Mongo's side, so the database in this case may quickly become a performance bottleneck. If that looks better suited for your needs (i. e. if you plan a lot of many-to-many relations), consider switching to PostgreSQL, it has some highly unusual datatypes for a relational DB (hstores, arrays) that may help.

• Do you mean I should have a field called items in the users collection, and I just add new sub-documents to the items field? Mar 19 '15 at 13:30
• Isn't that bad design? Since everything is cluttered into one collection. Mar 19 '15 at 13:31
• I just merged a couple collections into one haha. I guess there are some tradeoffs but it's working mostly according to plan. Mar 19 '15 at 15:51
• @nevercode what I say is make each user his own items. Right inside each one. Mar 19 '15 at 16:59

Usually, in a database, you'd make a table of all the items in your game, make a table of all your players, and make another table, the Inventory table, that stores entries consisting of Player ID, Item ID, and Item Quantity. If you were using another structure to hold your data I'd probably be inclined to tell you to give each player their own separate inventory so you don't have to try to find items at the bottom of a long array (or something) but databases are kinda designed for querying so it wouldn't make a lot of sense to use a database like you'd use an OO class.

• Lets say a user acquired two "long sword"s, and he upgrades one of them to have +1 dmg. I'll have to have a way of differentiating between those two swords. Mar 17 '15 at 19:04
• If you have modifiers for items you can just add a column to your Inventory table, add one longsword in for your player with no listed modifiers, and then another one listed with a modifier. Mar 17 '15 at 19:13
• would the inventory table be shared by all users? Mar 18 '15 at 16:01
• I have to note that this course of action is fine for relational DBs, which don't include Mongo. In Mongo it may be best just to embed (not reference) items in a collection inside the player document, and keep each item mostly self-contained, so you can freely alter each of these. Mar 18 '15 at 21:34
• It would hold information on all users, yes. Users shouldn't have access to any of the tables directly, they'd only indirectly access the inventory when they login (server sends client list of items) and when they add or remove an item (client updates immediately and requests the server modify database). It seems a little counter intuitive to put all user inventories together if you want to access them efficiently, but it's often faster to access one big table than to manage a number of little ones, particularly if a database is getting many requests. Mar 18 '15 at 21:40

It depends on what your items are like. Many games will just have a table of item types, and then the character record will list how many items of which types that character has. Only if you have unique items (special unique artifacts, or customizable items) might you want/need separate tables with records for each actual item, with links back to the owner, and perhaps not even then, though there might be reasons to do it that way depending on your game design.

Given the detail you added in the comment here, I'd suggest a table of characters, a table of item types, and then a table of actual items, with a column for the item type id, a column for the owner id (I would use an integer ID not an email string), and then you can add columns to that table if you end up having properties for specific items (for enhancements, damage to the item, special conditions, etc).

There can be a bit of a performance tradeoff in terms of looking the information up in the database, but probably you will write your code so you only look up the info from the database rarely, and keep local copies in memory when you are frequently re-using the information during play.

• Right now I only have static items in mind, but thinking about the possibility of adding item enhancement feature in the future, maybe it will be easier if I go the an entry for an item way now so I dont have to convert then Mar 17 '15 at 19:00
• @nevercode Ok, I added another suggestion above in that case. Mar 17 '15 at 19:12