Gamasutra is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

Gamasutra: The Art & Business of Making Gamesspacer
MMP Database Mini-Cookbook: A Half Dozen Recipes to Aid Development
View All     RSS
July 14, 2020
arrowPress Releases
July 14, 2020
Games Press
View All     RSS

If you enjoy reading this site, you might also want to check out these UBM Tech sites:


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

September 22, 2004 Article Start Page 1 of 3 Next

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
Recipe 2 - Managing Localization Data
Recipe 3 - Character Inventory Management and Shared Banking
Recipe 4 - Mapping Class Inheritance to Database Tables
Recipe 5 - Packaging Data to Minimize Server-Client Bandwidth
Recipe 6 - Shared Game Configuration Parameters

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.

Figure 1 - Table with Columns Needed To Create Unique Id

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:

ShardId NextUniqueId
13 1

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.

CREATE PROCEDURE GetUniqueIDBlock(@count int = 50000, -- default
                                  @startId bigint OUTPUT)
  set nocount on

  if @count <= 0
    raiserror('Must request at least 1 unique ID in block', 16, 1, 1)

  declare @value bigint, @shardId tinyint

  -- Get current values from db
  SELECT @shardId=ShardId, @value=NextUniqueId
  FROM ShardConfiguration

  -- Is our id space still good for the requested block?
  if 72057594037927935 - @value >= @count
    -- Generate the starting id for the request block and
    -- update db with the next valid value
    set @startId = (72057594037927936 * @shardId) + @value
    UPDATE ServerParameter
    SET NextUniqueId = @value + @count
    raiserror('The unique counter has exceed 56 bits of precision, a
    new unique ShardId must be assigned', 16, 1, 1)


The NextUniqueId column on the table is capped to count no higher than 256 - 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.

Figure 2 - Localization Tables

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:

GameTextTypeId GameTypeTextDesc
1 User Interface Element
2 Help Text
3 NPC Dialog
4 System Message


LanguageId LanguageDesc
1 English
2 Korean
3 French


GameTextItemId GameTextItemDesc GameTextTypeId
1 OK 1
2 Button Cancel 1
3 In Game Trading 2
4 John Hail 3
5 Alex Introduction 3
6 Server Restart 4


GameTextItemId LanguageId TranslationText
1 1 N'OK'
1 3 N'Oui'
2 1 N'Cancel'
3 1 N'To trade with another player…'
5 1 N'I am Alex'
5 1 N'Je suis Alex'

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

CREATE PROCEDURE ExtractGameTextByLanguage(@language tinyint) AS
  set nocount on

  SELECT GameTextItemId, TranslationText
  FROM GameTextLanguage
  WHERE LanguageId = @language

CREATE PROCEDURE ExtractGameTextByLanguageAndType(@language tinyint,
                                                  @type smallint) AS
  set nocount on

  SELECT GameTextItemId, TranslationText
  FROM GameTextLanguage A, GameTextItem B
  WHERE A.GameTextItemId = B.GameTextItemId
  AND LanguageId = @language
  AND B.GameTextTypeId = @type


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.


Article Start Page 1 of 3 Next

Related Jobs

Mountaintop Studios
Mountaintop Studios — Los Angeles, California, United States

Engine/Systems Engineer (remote)
Mountaintop Studios
Mountaintop Studios — Los Angeles, California, United States

Graphics Engineer (remote)
Yacht Club Games
Yacht Club Games — Los Angeles, California, United States

Senior 3D Technical Artist
Mountaintop Studios
Mountaintop Studios — Los Angeles, California, United States

Network Engineer (remote)

Loading Comments

loader image