MMO and databases

I would do the tables like this.

CREATE TABLE items (
ID INT NOT NULL AUTO_INCREMENT,
Name varchar(255) NOT NULL,
other values...,
PRIMARY KEY(ID)
)

CREATE TABLE bag (
ID INT NOT NULL AUTO_INCREMENT,
PlayerID INT NOT NULL,
ItemID INT NOT NULL,
FOREIGN KEY (ItemID) REFERENCES items(ID),
FOREIGN KEY (PlayerID) REFERENCES player(ID),
PRIMARY KEY(ID)
)

Then you can query for players bag "SELECT * FROM bag WHERE PlayerID = 1;" and "SELECT * FROM items WHERE ID IN (31, 26, 63, 42...);" of course the PlayerID in the bag query is the players ID who's bag you want to look at and the items query ID values are the ItemID's from the bag query.

Here's some links that may help regarding spatial indexing:
http://workshops.opengeo.org/postgis-spatialdbtips/introduction.html
http://technet.microsoft.com/en-us/library/bb964712(SQL.105).aspx
http://msdn.microsoft.com/en-us/library/bb964714(v=sql.105).aspx

As far as inventory, TMKCodes' approach seems fine. In all or most cases inventory is irrelevant for spatial positioning, effectively in a mini-universe. The trick when using a RDMS is the crazy variety of attributes an item or character may have. For example what buffs apply, what resistances things have, etc etc. I don't have a great answer so far. You can have very wide tables with lots of attributes, spin off sub-tables, serialise data in a single column, etc etc. How does it work if you add new features? Nobody wants to modify the schema of a running MMO if they can help it.

A full-blown RDBMS is generally overkill for a true MMO. You want your data layer to be mostly write-heavy in sequential blocks for disk throughput and performance on the actual database servers; pulling nontrivial queries against your DB is going to murder yourscalability in a heartbeat. Instead, you should use the data layer for persistence only, and do your nontrivial logic up in the game logic itself.

As for generating informational queries and such, it's generally advisable to stream data from the game into a separate data store that is used exclusively for reporting and statistics. This information can then be data-mined at will without disrupting the performance of the running system. Only real drawback to this approach is that you'll have to put up with a small time delay while data is aggregated and collected from all your game servers, but it's generally not important to have hard realtime stats on most of those queries anyways. If you really do need hard realtime numbers, build a reporting mechanism directly into the game logic that you can access at will.

Database usage in MMOs is largely a matter of a few simple best-practices and a lot of one-off decisions that have more to do with the project's specifics than overarching principles. What works well in one game might make zero sense in another, even if they "feel" like similar gameplay experiences. A lot of it is down to the actual technology used to run the game itself. So there might be enough collected wisdom to write a medium-sized article or something on the subject, but I doubt it's really enough to merit a complete book.

A query for "what's in my inventory" does not take forever if you have proper indexes.

However, you don't want a relational database table to grow too big, because that leads to poor performance. Instead, you will need to shard the storage somehow. Many MMOs simply shard by "world instance." Shared-world systems will have to shard by consistent hashing or similar.

Once you shard your data, you probably can't do transactions across shards, so no inter-server player trades without escrow for example. Unless you build your system using the large, expensive, "enterprise" versions of DB/2 or Oracle. Which you probably shouldn't :-)

The Cryptic Studios guys had a GDC presentation a few years ago about how their design migrated from RDBMS through a number of caching and re-structuring steps to an object-store database. Other developers stay with RDBMS-es. It all depends on what your particular trade-off between run-time performance, willingness to cache in RAM, cost of hardware, cost of development, and game rules is.

Even though a RDBMS is quite unsuited to this task, RDBMSs have been and still are used for even less suited tasks (and, successfully!). One can always do some design compromises, shard out, or throw more expensive hardware at it. Is it the perfect approach? Surely not, but it's what works.

RDBMS/SQL comes with 3 very major advantages:
1. It's ubiquitous, there are literally millions of experts on the market, bindings for every language, and middleware readily available
2. The respective implemenations have been tested in millions of installations, concepts have been tested millions of times
3. ACID compliance (which is equally important as it is hard to get right) is understood as a natural, basic features in all mainstream implementations, and it has been tested on millions of installations. It will "just work" and unless you explicitly do something very stupid, it won't bite your ass when you don't expect it.

That said, there is nothing wrong at all with no-SQL key/value stores, especially for game data.

Thanks for the comments, everyone.

I'm looking at various NoSQL systems right now. They seem to fit closer to the mental model you have for how an MMO works. MongoDB especially seems promising. It has spatial queries and indexing, which you'd need if you want to have, say, large numbers of items floating freely in the world (ala Ultima Online, if anyone remembers that game). In the case of storing bags of items, it also has arrays natively supported. So you could have bags represented as documents in a collection (ie: as rows in a table), with items stuffed in to an array inside each bag document (row). It feels very similar to how tables work in Lua, actually. It seems to map pretty well to OOP. The primary downside is that it's not ACID compliant. So transactions (like moving an item from one bag to another) get tricky and you probably have to manage a two-phase commit manually.

...

To give a different usecase example, I'm trying to scope out how technically possible a "living world" MMO would be. For instance, I'm thinking of trees that spread seeds that grow more trees, with players allowed to chop the trees down or manually move seeds around. To handle it in a general way, I'm imagining that all entities that exist in the world can register themselves to run certain scripts at some future date. So on the server side you'd need to periodically query for entities that are due to run scripts and run them. The scripts can update the state of the entity, change behavior based on neighboring entities, spawn new entities, etc., and write the results back to the database. Clients could request spatial queries to get all the entities in a given area that they're playing to find all the visible entities so that the client can display them. The number of dynamic entites in a given area of the world could be quite large.

You'd probably want to cache highly active entities (like animals) in RAM, and only push changes to the database periodically. Or even treat things like animals totally differently from more static entities. But let's ignore that and just call the entire game state recording mechanism the "database".

As a single player game this wouldn't be so unusual, but making it work as an MMO would be quite difficult. I imagine a traditional RDBMS would quickly choke on something like ths.

As I said before, you do not want to be building that kind of logic in your data persistence layer. That's just bad architecture.

You should have a game server that manages the spatial lookups, the script invocations, and so on. It would only need to go out to the database rarely (maybe every few seconds) to persist state changes to the world, and you'd only ever need to read back from the data layer when starting up a new server instance.

Sure, you can do that. But doesn't that mean you basically built your own RAM-only database technology with spatial queries, etc.? Which has all the benefits and pitfalls that rolling-your-own-solution does.

If we assume that "the database" lives across multiple boxes, and the servers running the game logic/script updates are yet different machines, you've basically built a local database cache in RAM on each of the game logic servers, that communicate changes to "the database" lazily. Which sounds a lot like some sort of eventual consistency/MVCC database system (maybe not exactly? I'm not sure what vocab word fits here)

So if you're going that route anyway, wouldn't it make sense to actually use some sort of MVCC database system directly? A good database system should be able to handle caching things in RAM just fine, so the primary drawback is that the local database cache has a different application memory space. But I think there are some technologies that you can integrate directly in to your application (I think BerkeleyDB can do this?)

If all your "game" is is a giant database, then sure, you could do that.

As soon as you have to integrate actual game logic with this separate data system, you will hurt in the long run. The problem with externalizing all your storage is predominantly access latency. Unless every game server is also a database shard server (waste of hardware) you will have to hit the network to grab every single piece of data you want. Now you have an unreliability concern and a latency problem - expect on the order of a couple milliseconds for every data fetch instead of microseconds at worst. Plus, if your network flakes or a database server goes down, you're screwed; in a persistence-only model, you just keep on chugging and hit the data store as soon as it becomes available again.


Building scalable, performant systems is often counterintuitive. "Rolling your own" may be a sin when you're talking about linked list classes, but when it comes to building an MMO, you need to have very domain-specific solutions. Integrating with a data store back-end is fine, but building everything around off-the-shelf middleware is a surefire way to kill yourself as soon as you hit scale.

If you choose MongoDB, make VERY SURE you understand the data persistency guarantees (or, rather, non-guarantees) you get. Also, make sure you understand how to make consistent backups of MongoDB and are willing to go through with such a system.
Personally, I like (and use) Redis a lot for semi-structured-data stores.

You might also want to consider some new solutions made by Parallel Universe (my company). We provide an embedded, in-memory spatial database called SpaceBase, that also helps you parallelize game code, and an open-source data distribution layer called Galaxy. These solution have been built specifically for MMOs (and some other, similar applications) and work at extremely low latencies. They are not as complete as some NoSQL databases, but that is precisely because we realize, as ApochPiQ said, that to handle specific game requirements there will be some problems you will need to solve yourself with ad-hoc solutions, but we try to help with the really hard general problems.