MMP Database Mini-Cookbook: A Half Dozen Recipes to Aid Development

It is a sure sign that a particular technology or practice has taken hold in the industry when conversations between developers no longer ask "if" or "how", but instead have progressed to discussing best practices and patterns. This new shift has occurred with regard to MMP game development and relational databases, and in recognition of such a move, this article puts forth half a dozen recipes for tackling common problems in massively multiplayer online game.

The recipes each follow a common format: a recipe name that allows readers to quickly discern their interest; a problem statement that frames the problem being solved; and a solution which both summarizes and then illustrates the solution in sufficient detail for the reader to be able to follow and implement.

The specifics of the information presented are particular to the relational database I currently work with, Microsoft SQL Server 2000. However, all of the concepts are elementary enough that applying them to a different product offering should be reasonably straightforward.

An important note regarding the figures presented - as tables are linked with connectors, the symbol on the end of the connectors signifies the type of relationship involved. A connector with 2 keys represents a 1-to-0/1 (or dependent) relationship. A connector with a key on one end and the chain-link like symbol represents a 1-to-many relationship in the direction of the chain-link.

These recipes appear in no particular order, but the list is presented to allow skipping directly to the ones of interest.

Recipe 1 - Unique Identifiers for Painless Game World Transfer

Problem: The industry standard for managing persistent worlds of large numbers of players is to have multiple server sets, or "shards". A player logs in and typically selects one of these shards to make his home and grows his character(s). At some point in time, there comes a need to migrate the player away from the initially selected shard. Most often, this comes about because the player wants to join friends that have established characters on another shard, but there can be publisher-initiated reasons which require the ability to switch a player's character to another server.

The traditional obstacle to this has been the inability to uniquely identify a player's in-game possessions, except within the shard they started on, since each shard can only ensure uniqueness within its own space. For example, an item on Shard1 with Id 84774 is unique on that shard, but this same ID on other shards will likely represent something different altogether.

Solution: Provide the ability to generate globally unique identifiers within the context of a shard without requiring cross-shard coordination, as this may introduce bottlenecks or undesired dependencies.

First create a table in your database matching the one in Figure 1 (see also recipe #6). The table and column names are not critical, but the data types assigned the two columns shown must match. The first column must be a numeric type requiring a single byte (or 8 bits) to represent. This will yield the ability to have 256 different ShardIds (or 128 if the db only support signed types, and negative ids are unacceptable), which should be quite sufficient to cover even the most popular new title. The second column must be a numeric type that can handle 64 bits of precision. This column (NextUniqueId) is simply incremented by one each time a unique id is assigned in game.

There will only be a single row in this table. Within each shard database, ensure that the given ShardId is different from every other, and the NextUniqueId starts at 1. When queried, the table should have a result similar to this:

Add the stored procedure shown below to the database. When called, this stored procedure will reserve and return the starting id for a reserved block of unique ids that can be used by the game for assignment. As the block gets close to being used up, the game would pre-emptively request a new block.

The NextUniqueId column on the table is capped to count no higher than 2⁵⁶ - 1 so that only 56 bits of its precision are used. The high eight bits of the resulting 64 bit unique ids are filled with the value from the ShardId column, resulting in an id that will be unique across shards. By assigning each player's game possessions a unique id at creation time, it makes it trivial to extract and remove a given player's data from one shard's database, and simply insert it into a different shards database to effect the transfer.

Recipe 2 - Managing Localization Data

Problem: Managing all of the various types of game text in an MMP game is a daunting task, particularly when the game needs to be localized into various languages. Providing raw data for localization specialists to do their task can also be difficult, if they are not programmer-savvy.

Solution: Build a set of tables in the database to capture and organize game text. Because the content resides in a database, there are many development tools (such as Visual Basic or C#) that make it very simple to build a forms-based application. This can be used by non-programmers to enter and modify language-specific versions of text. For deployment purposes, the data is extracted and organized into a representation readable by the game client (such as a resource file), and accessed when the client is running for display.

First, implement the set of tables shown in Figure 2. The Language table has a single entry for each of the languages to be supported in the game. The GameTextType table has one entry for each of the different types of text that might appear in the game - for example, user interface text, NPC dialog, help text and system messages. The GameTextItem table contains an entry for each text item in the game, categorized by one of the GameTextTypes. Finally, the GameTextItemLanguage table contains an entry for each game text item, the language, and the actual text in Unicode. Note that the TranslationText column is a varying length column to support text of all sizes up to 4K, and that its data type supports multi-byte characters.

Once implemented, the contents of the tables might look like the following:

To extract the data for use on the client, the following stored procedures should be created in the database:

The first stored procedure, ExtractGameTextByLanguage, will allow the creation of a single data source which has every piece of text for a given language. The second stored procedure allows extraction of the data by the given type and language, as it may be desirable to organize each distinct source by usage. This could create one file that contains all the user interface text, another for NPC text and so on.

______________________________________________________

Recipe 3 - Character Inventory Management and Shared Banking

Problem: An MMP game typically allows a player to create multiple characters, in order to experience the variety of content created for various classes available in the game. Common to all characters, however, is the ability to acquire and carry items in a personal inventory. Players can usually stow items away from a character in a mechanism often known as a bank. It is desirable that items in a bank be accessible to all characters that belong to a given player, so those items can be securely transferred between characters. Additionally, various game systems may require that items be associated with the player or particular character, but not allows direct manipulation by the player. For example, there could be a quest inventory where items are awarded and removed without player intervention. Persisting and managing the various in-game storage types can become tricky without a streamlined design.

Solution: Separate the details of the persistence of any given item from how the items are collected within various game storage mechanisms. Create a single PlayerInventory table that represents how an item is being stored, with the flexibility to move items back and forth amongst player, character and inventory types, while avoiding having to add or delete entries except when items are explicitly added or removed from the game.

In the Figure 3, each player is represented with a single entry on the Player table, created when a player logs into the server set (or shard) for the first time using their PlayerUserId (AKA user login name). The unique PlayerId (see recipe #1) is then used to represent the player in all subsequent relationships in the database. The additional supporting table to note is the PlayerCharacter table, which has an entry for each separate character owned by a player.

The core of this recipe is represented in the InventoryType and PlayerInventory tables. The former simply identifies the various types of in game mechanisms used to store items. Its contents might be as follows:

The PlayerInventory table contains entries that record the PlayerId and ItemId, acting as the key for the table. This means that, for any given item, we always know which player it belongs to, and we have the associated id required to uniquely identify the item (as well as look up the item details). Additionally, the InventoryTypeId is specified to record where the item is currently stored, and the optional CharacterId is only populated if a particular character currently has the item in its possession.

To illustrate, the following might be entries for a given player:

All of the items ultimately belong to the player with the PlayerId value 1001. However, the items themselves are to be found in different "locations". The first item, with ItemId of 3838498349, actually resides in the bank, as shown by the InventoryTypeId of 2, and the fact that there is no associated CharacterId for this row. The next two items, 1323884775 and 1372387774 respectively, are found in the personal or character inventory (type 1) of the character with CharacterId value 37483738787. The last 3 items are all associated with the character having a value of 73838483748, but it turns out that each item is found in a different "location" on that character, in the quest, crafting and personal inventories respectively (types 3, 4 and 1 respectively).

Once the structure is in place, the following stored procedure can be used to transfer items around:

In other words, using our example data, ItemId 3838498349 can be transferred from the shared player storage (bank) to the personal inventory of character 37483738787 by executing the following statement:

EXECUTE TransferItem 1001, 3838498349, 1, 37483738787

Character 73838483748 can have Item 1343984398 transferred to the bank by executing the following SQL statement:

EXECUTE TransferItem 1001, 3838498349, 2

(Please note that by excluding the characterId argument, the value gets set to NULL on the row, desired when an item is moved to shared storage.)

Along with the following stored procedures that add and remove items to/from any inventory type, we have all that is needed to manage item storage from within the game:

One final point of interest remains. We can ensure that items don't get "lost" from a player by adding some referential integrity via a trigger. The trigger would check that, when a row gets written or updated on the PlayerInventory table and the CharacterId is not NULL, the combination of PlayerId and CharacterId currently exists on the PlayerCharacter table. This would ensure that the row added is guaranteed to be associated with a valid character belonging to the player:

______________________________________________________

Recipe 4 - Mapping Class Inheritance to Database Tables

Problem: Developers of modern MMP games are very likely using an object-oriented language such as C++, Java or Python when writing code. These languages provide a significant amount of benefit through their support of the concept of inheritance. This is the ability to describe a new class as a specialization of a previously defined class, gaining all of the existing functionality, and only having to define what makes the new class different.

For example, if a game already has an Item class that can be stored in inventory and traded, when the Weapon class is defined, it can inherit from Item. The programmer only needs to implement what makes a Weapon distinct from an Item, but it will act as an Item in all existing functionality in the game.

A relational database does not natively support the concept of inheritance, but it would be highly desirable to be able to represent data in a manner that closely resembles the run-time representation. To follow on from our example, it would be great if we could see both Item and Weapon data stored in the database, and that it does so in a manner that correctly models the inheritance relationship.

Solution: Capture the classes that are desirable to model and persist into 2 sets of tables, with dependent relationships to represent the in game inheritance relationships.

The first set of tables should depict the classes in terms of the class level variables - that is, those attributes that do not vary from instance to instance. For example, for a Weapon class, this might be the base range distance for the weapon to successfully hit.

The left-hand side of Figure 4 shows an example of a mapping of an inheritance relationship between 3 classes: a base GameObject class, an Item class that derives from it, and a Weapon class that derives from the Item class. Note that there isn't a table for every type of GameObject class; instead these are captured as separate rows on the GameObjectClass table, because one of the columns is the class name itself. The ItemClass table has rows for those classes that are items in the game (defined as something a player can acquire), but not every GameObject is an item.

The WeaponClass further specializes Item, in that they can be acquired by players and have an inherent value in the game, but not every item is a Weapon. The type of association between the tables is worth noting. GameObjectClass has a 1-to-0/1 (or dependent) relationship with ItemClass. This ensures that if something is going to be an ItemClass, it must also be a GameObjectClass, but not vice-versa. The same is true with ItemClass and WeaponClass - should a ClassId value appear on the WeaponClass table, it must also have a corresponding row in the ItemClass table.

The biggest benefit of class tables is that the data in them will remain static as the game runs. This means that their contents can be pre-loaded into the game server when they initially launch, and should never have to be re-accessed until the next time the game is restarted.

The second set of tables, shown on the right hand side of Figure 4, represents the instance level tables associated with a given class. In the example, a GameObjectInstance row may have a corresponding ItemInstance row, and an ItemInstance row may have a corresponding WeaponInstance row. The columns that appear on these tables should only be those that can change at run time. For example, on the GameObjectInstance table, note the WorldLocation columns. These represent the position of the object in the game world. Since this will differ on a per object basis, they must by definition be placed on the instance table.

The GameObjectInstance table has an important column on it, the ClassId. This is the id of the class that any object instance is associated with, and must be a valid id from the GameObjectClass table. While the example suggests that there has to be corresponding instance table for each class table, this is not the case. It is certainly possible that a class in the game can consist of purely class attributes, or purely instance level attribute. If so, that certainly can be modeled within the construct of this recipe.

A couple of cautions are in order with regard to instance tables. The first is that, for performance reasons, the actual count of instance tables being created should be kept to a minimum. Since instance data can change at run time, the number of stored procedures that may need to run to keep an individual item's data in correct state could get large. By erring on the conservative side in table count, and only generating updates for tables when the data has actually changed, we should be in good shape.

For the same reason, it's important to resist the temptation to build an instance table that allows more than one corresponding row per given game object. If your game calls for repeating rows on an instance table, work with the game designers to determine if a limit can be set for how many are required, and attempt to implement the solution in terms of multiple columns on a single row. It's better still if instance level data can be re-categorized as class level data to avoid any run-time penalty.

The following stored procedure demonstrates how to retrieve instance level data from the db while the game is running:

When executed, this stored procedure only returns a single row, regardless of how many instance tables are involved in the join. For any given game object, the value of the columns will be NULL, should there not be a corresponding row on a dependent instance table.

Recipe 5 - Packaging Data to Minimize Server-Client Bandwidth

Problem: MMP game developers would like to provide interesting visual and aural responses to players when they trigger an event. The game server process ends up being the best place to trap such events for processing. However, communicating the data required to provide the desired response can be heavy on bandwidth, and require the server to be aware of client-only subsystems such as audio, animation or particle systems.

Solution: You can abstract the data required to perform visual and aural choreography into a package, or related set of data, that can be referenced via a single identifier. It's then possible to transmit the request to the client as an implementation-agnostic request. The corresponding data is then looked up on the client, and passed on to the appropriate subsystems for processing.

In the Figure 5, data required to play back audio in various ways is captured in the table called AudioPackage. An audio package row identifies a package, including a name, the audio file referenced, the manner in which the audio should be played (from the AudioType table), a time range for playing the audio, and the volume that the audio should be played at.

The contents of the tables might be as follows:

With the contents of the AudioPackage generated for use on the client, it would then be possible to execute the following code on the server (shown as pseudo code):

Obviously, this general idea can get much more complex (co-ordinating object animation, particles and sounds, for example), and yet retain the same level of abstraction from the server's point of view.

Recipe 6 - Shared Game Configuration Parameters

Problem: An MMP game usually requires multiple processes, running on multiple machines to support the amount of people in a game world. A game usually has a set of parameters that these processes start up with in order to configure the game. Normally, these would be read from a configuration file, but having to deploy these to every machine running one or more processes for the game can be mistake-prone.

Solution: Build a table in the database that contains a single column, representing each configuration parameter needed by the server processes. At process startup, the servers read this table to retrieve the data required. Any needed changes are done in one central location, and every server gets the change the next time it is restarted. Figure 6 shows an example of such a table - only 1 row will actually exist on this table. The columns on the ShardConfiguration table shown below are simply representative. Feel free to add ones that are appropriate to for your game and environment:

______________________________________________________