Resource Guide

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

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.

	Figure 3 - Player Inventory Tables

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:

	CREATE PROCEDURE TransferItem(@playerId bigint,                               @itemId bigint,                               @type smallint,                               @characterId bigint=NULL) AS   set nocount on   UPDATE PlayerInventory   SET InventoryTypeId = @type,       CharacterId = @characterId   WHERE PlayerId = @playerId   AND ItemId = @itemId

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:

	CREATE PROCEDURE AddItem(@playerId bigint,                          @itemId bigint,                          @type smallint,                          @characterId bigint=NULL) AS   set nocount on INSERT INTO PlayerInventory values (@playerId,                                     @itemId,                                     @type,                                     @characterId) CREATE PROCEDURE RemoveItem(@playerId bigint,                             @itemId bigint) AS   set nocount on   DELETE   FROM PlayerInventory   WHERE PlayerId = @playerId   AND ItemId = @itemId

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:

	CREATE TRIGGER PlayerInventory_ValidateCharacter ON PlayerInventory FOR INSERT, UPDATE AS   DECLARE @playerId bigInt, @characterId bigint   -- retrieve the values being written to this table   SELECT @playerId=PlayerId, @characterId=CharacterId   FROM INSERTED   -- early out if we don't need to check   if @characterId IS NULL   begin     return   end   -- now check it exists on PlayerCharacter   SELECT PlayerId, CharacterId   FROM PlayerCharacter   WHERE PlayerId = @playerId   AND CharacterId = @characterId   if @@rowcount <> 1   Begin     RaiseErr("PlayerId/CharacterId Combination Invalid", 16,1,1)     rollback     return   end

______________________________________________________

join | contact us | advertise | write | my profile
news | features | companies | jobs | resumes | education | product guide | projects | store



Copyright © 2004 CMP Media LLC

privacy policy | terms of service