I recently started a project in my free time, which is about constructing a very very simple mmo 3-tier architecture (for a max player count of 100).

I came to the point where I have to decide what data can be stored in the backend (MySQL). Constantly storing a players position seems unefficient to me.

The same goes for item properties like damage, item level and other.

I wonder how this thing would be done by an expert. My idea was to store player data only if something happend (like level up) and save players position every 5 minutes or something.

For the item properties part, i thought of a construction, that the item data may be stored in the backend, but initialy loaded once the server starts up. So the item properties would be stored in the memory of the server. If I change something at runtime of an item, this item could be reloaded again.

I hope you understand what I am trying to do and maybe you can give me tips or links to some articles.

Save persistent world state (eg. items in the world that you intend to persist across a reboot) when placed or moved (which is usually rarely)

Item properties - and character properties - need some careful consideration before you think about storage. In particular you need to consider:

Which aspects are shared across all items of a certain type (eg. material, 3D model, item type), and which aspects are unique for each instance (eg. magical charges left, damage done to the item)

How do you want to edit the shared aspects during development?

How do you anticipate patching shared aspects after release?

People familiar with databases are often inclined to put everything in the database, maybe with one table for the shared data and one table for the instance data. However this starts to work very poorly when you come to updating the data, because your data on your development machine is likely to have primary keys that clash with data on the live machines, assuming new objects have been created during play. You might get away with it if you only ever add to the shared data table - ie. never delete shared data, and never add instance data.

In general, after having experience of working both ways, I prefer to handle as much of the data as possible outside of the relational schema. I load shared data in from whatever format is easiest for designers to work with and which is easy to patch and merge (eg. text files or some derivative), and instance data in the database contains a reference to the relevant part of the shared data.

While I'm here I'll mention that you should be careful when considering primary keys for any data (whether in the DB or not). Autoincrements and sequences are a bad idea when it comes to merging. Try to use natural keys rather than surrogate keys if you can, because the natural keys will remain correct across development, test, and live servers.

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.

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. By calling a function that doesn't set the position but increments it by how fast your character moves so by changing your x,y,z in memory doesn't actually move you in the server (maybe it would look like it till you moved forward) so you would actually be getting your position from the DB more often then actually storing unless you are moving... Any thoughts on that idea? This was more food for thought to discuss since you stated "Constantly storing a players position seems unefficient to me".

We dont even let the player control their position. The server should have authoritative control over this but to do that you have to finesse it a bit since this can cause some lag if latency ever exists.

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.