I recently took the step to replace an SQL DB (MySQL) with a NoSQL (MongoDB). It turned out quite a lot of things got much easier to manage, but there are maybe disadvantages I am not yet aware of.
A simple comparison between SQL and NoSQL is that a SQL database supports joins and atomic updates of tables, while a NoSQL supports more flexible documents (rows) that can contain sub sets and arrays of items. In my case, I save one player for each document, including an array of inventory objects. The periodic auto save only updates some key facts of the player in this document.
This is very similar to my situation. I worked with an SQL database on a commercial MMO and then also with one on my personal MMO project, but recently I moved to MongoDB. I also have one document per player with the inventory embedded. The data often contains IDs to things outside of MongoDB however, eg. unique item identifiers created by designers.
Disadvantages of MongoDB (and some other NoSQL databases) include:
- Arbitrary queries are more complex (eg. look up all documents in one collection which have a sub-field that exists in any of the records in a different
table - trivial in SQL, tricky with documents) and typically less efficient
- Reliability is generally not as good (especially with MongoDB, although it has got better recently with journalling etc)
- Non-portability of the data definition or query languages
- Atomic transactions not always possible without changing your schema (eg. you can't guarantee that you can amend 2 documents together atomically)
Despite all that however, I prefer MongoDB for game use. Being able to store JSON pretty much straight into the DB is literally orders of magnitude easier than trying to get everything to fit nicely into tables and columns. That said, there's no reason why you can't just use PostgreSQL or MySQL as a key/value store, and you can get back some of the transactional and reliability benefits that way.
Just from a hacking aspect of things... I have wrote memory editors for MMO's that allowed you to teleport across the map to any x,y,z location. I am not sure on the most efficient way of doing this so maybe I am wrong but I would think storing the players position is something you would do regularly to avoid this.
You misunderstand - the position is always 'stored' in memory, but we are talking about a separate relational database. You don't need to regularly store the player's position in that database, because you always have the latest information in memory, which you can check against to prevent hacking. It only needs to be written to the database periodically, or on logout, so that if there's a crash the server will know where to place the player when they next log in.
Edited by Kylotan, 21 February 2013 - 01:30 PM.