Game Development Stack Exchange is a question and answer site for professional and independent game developers. It only takes a minute to sign up.
Sign up to join this community
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.
items. Right inside each one.
\$\endgroup\$
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.
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.
