I'd put it on the same server as the game to start with. You can move it to another server at a later date if you need to.

As for when to perform updates to it, I'd suggest when they log off (as you say) and then just at regular intervals (maybe once every couple of minutes). I don't think you need to do it much more than that...

By the way, how are you planning to store the "world state"? For example, if I drop a sword in the middle of nowhere, how does the server remember that? Is it just kept in memory, or do you want that to be persistent (in case of server crash, for example) as well?

Personally, just keeping it in memory is probably "good enough" to begin with, but you might want to think about it for the future...

Why would you host the database on a web server? It should be hosted in proximity to where it is going to be used the most. Unless you have the most amazing web site ever and everyone always checks out data from your game, but nobody plays it, it should be on the same computer (or network) as the game server. It is best to just leave it on the same machine while you can. You should worry about your game's performance first, then the web site.

As for synchronizing the database, you can find more on this all over this forum and the net. The easiest way is to just synchronize events as they happen. To improve performance, you can do this non-blocking where possible, but this increases complexity quickly. Another popular option is to "flush" data every so often all at once. Usually people will do this with a snapshot of the game so the snapshot can be saved while the world progresses. This is actually a much easier approach for decent performance since, if it takes too long, you just increase the time between synchronizations. Though if you really want the "massive" part of MMO, this wont work because there will be many servers reading from your database, so one server can't just horde data for an hour.

I think you first need to decide what your goals are. A database for a game with a few hundred players is and a typical game world is going to be easy - the game can likely run with just one server instance, and not enough will be happening for it to become a big bottleneck. If you want to go massive, though, with tens of thousands of players, this becomes a much larger challenge, and the approach is completely different.

For the past year+, I've been designing my second for-fun online RPG engine. My first one (vbGORE - see signature) used a very simple, very raw database structure that was sure to make database experts scream in pain. There were about 4 tables I believe (item templates, npc templates, users, and banned IPs), and only two of them (users, banned IPs) were ever even updated by the server. Each update just did a big fat update of every value. As ugly and weak as it was, there was nothing wrong with it - anything more would've just added extra complexity and ability to do stuff the game was not intended to do.

With the engine I am working on now, I am aiming for much more dynamic and customizable data. I have items that are created from templates, but any aspect can be altered (unlike my other engine, where items were just generated from templates, never instanced). I do not synchronize my world, which means that if it resets, all the maps and everything on them reset, too. This is no problem to me, since I have no plans yet of having a very dynamic world. But the introduction of instanced items requires that to be stored. It also requires relationships between the user and the item through their inventory. To allow for future expansion (in case I want to do more), it also needs to allow for any other object (NPC, map, etc) to be able to point to objects. Clearly, things get much more complicated.

I have done a lot of multiplayer game work, and a few game servers with database backends, but I have never done anything very advanced in terms of world synchronization (they have all been more like the first example I gave), so I can't help you a whole lot there. The first thing you want to do is learn about databases and using them - do you know how to do joins? Examine the efficiency of your queries? Know what you should use indexes/keys on and why? There is tons out there on the internet already to help you with this. Once you understand databases, design a database model that fits your needs. There is no reason to design this incredibly normalized, sexy looking database structure if you aren't going to use the benefits it brings. Again with vbGORE, I used a very simple structure, making my database nothing more than a glorified text file. It would've sucked to try to expand it to do something more, but I didn't care, because I knew I was not going to do that anyways.

Once you understand what you are trying to do, and understand the tools you have available, you can decide what you need to do.

What we use for our game is a mix:
- some properties of the character are deemed so important that they are saved as they happen. Ie, if you learn a new feat (talent), skillpoint etc.
- Some properties are not that important, and are only saved when you zone; like health for example. (if server crashes and you are able to log in with full health, its not a big deal)
- item stuff is always handeled in a transaction, so it can be rolled back if it fails or if the game server crashes when doing the trade.

As other people in this thread has suggested, put the database as close to the server as you can. if its on localhost (same machine) thats ok.

Your data model will have huge impact on weither you can save stuff as it happens or if you have to save in intervals.
- Some games like to store everything in a binary format (they have a character table and a datarow which just accepts binary data where they store everything) - now: if you want to store a single item here or change health, for example, you need to write the entire character. so saving stuff as it happens will now have a huge overhead, so saving it in intervals might be the only viable option.
- If you have a more normalized table structure (say, a CHARACTER and CHARACTER_PROPERTY table) saving a property like health for the character will block less, and have a less overhead. Another advantage is also that you can look at the data in the table without having a tool that can upack the binary data ;) What, you created an item that is too powerful? delete from ITEM where template = powerfulitem! and its done ;)

In my experience game databases usually use their cpu cycles to idle.

There is no harm in using just huge, non-normalized tables instead of binary tables, either - as long as your system can support it (which is very likely it can with a smaller game).

For my first engine, vbGORE, I originally did the game data in binary files. Performance was my biggest concern, which was why I chose a fast language like VB6 (sarcasm). It was just too much of a hassle to maintain. Every time you wanted to add something new to what was being saved, it required a conversion which was a pretty painfully long process for something so simple. BLOBs will result in the same thing needing to be done.

Do yourself a favor, and focus purely on design and maintainability from the start. A good design will reduce / prevent you from having to restructure your data to fit new features or to be able to handle more players, while a more maintainable system will allow you to spend less time dealing with change and expansion. Doesn't matter how slow or fast it is before it is public, what matters is how fast it can be. Good abstraction will make it easier to improve performance, and a good design will make it easier to be more scalable.

With what _Kami_ said about prioritized updates, it is definitely a good idea, but can be very complex and difficult to work with. There was a discussion in this forum category a few months back I believe about just this. I'd recommend checking that out. I personally have nothing to say on that subject, since I have never used it nor spent much time researching / experimenting with it.

If this is a learning experience (and you're really creating an online RPG, not the "massive" scale version), then I suggest you keep the database as simple as possible. Store XML in one file per user, perhaps?

If you feel you need to learn SQL integration, then I would recommend using SQLite, as it's really easy to integrate, and free. In general, though, the most important part of any server development is to make sure to not block the main loop on I/O. Thus, all file I/O, and database calls you make should be in the form of requests, with a continuation that runs when the request completes.

Typically, you'll write a file system abstraction, and/or a database abstraction, that runs in a separate thread, and funnel requests to those threads and back. The next logical step is to put the database on the other end of a TCP socket, which means you COULD run it on another machine, or in another process, but could also still run it on another thread in the same process.