|
|
 |
 |  | | | | |  | |||
 | ||||||
          |  |  | ||||
|
Relational
Database Imagine you're building an MMOG, and after conducting some research, you decide that a relational database is the ideal solution to the needs of game state persistence, back office functionality (such as account management and billing), and even building game logic with a data driven approach. Congratulations, you have made an excellent choice! But before you get too carried away feeling good about yourself, be aware that there are many challenges to implementing your game on top of a database. Designing an effective schema for an online game is not a trivial task. In this article I will provide a foundation for making some of the key decisions that you'll encounter in regards to your database, based upon my years of experience in this field, most recently at NCSoft. Hopefully your development effort will culminate with shipping a game built on top of a robust database solution, and your organization will be poised to take advantage of everything that it has to offer. Where appropriate, I will share specific comments related to Microsoft SQL Server 2000, the database product in use where I am employed. This should not be interpreted as a pitch for the product, nor an indication that other competing products are inferior. It just happens to be the software I am currently utilizing, and thus most familiar with. It
could very well be that the same reasons we chose MS SQL Server would
make a different product the right choice for your game. It really depends
on your specific needs, and no one knows these better than you. The information
presented here is designed to supplement your own analysis, to the extent
that it applies to your situation. My goal is to provide information to
help you make the right decisions in this regard. The information is organized by topic. Tackle the topics in any order you wish, as there are no topics that are prerequisite to others. Naming Standards Establish naming standards for all the objects that are created in the database. Use as many characters as needed to be both clear and descriptive when naming each object. Name objects so that they are easy to remember and can be guessed correctly most of the time by anyone on the team. For example, if during discussion about the subsystem that generates creatures for the game, the terms "Creature" and "Spawner" are frequently used, then there should be tables called Creature and Spawner in the database. It is not worth getting too creative when naming new tables that result from the association of existing tables. It's simply easier to concatenate the existing names together. So the association of Creature and Spawner becomes a table called SpawnerCreature, or CreatureSpawner if you prefer, making it very obvious which two (or more) entities are being related. When naming columns on a table, the goal should be to pick names that make it very obvious what each column is being used for. This is especially important on games with long development schedules and lifetimes like MMPs. It's all too easy to look at a table after a few months and have no clue what a column is used for. Good naming goes a long way towards preventing this from happening. Examples of column names one could expect to see: CurrentlyUsedFlag on the Spawner table to indicate if it is currently in use or HitPointAmount on the Creature table to store the hit point value of each creature captured in the table. Notice that in these names, the data type of the column is implied; one would expect a flag to have an on or off value, or that an amount would be some type of integer. Microsoft SQL Server allows the ability to create user-defined types in terms of existing types. This can be leveraged to further standardize data type assignment. Suppose that each in-game object is identified by a bigint, which is the equivalent of a 64-bit value. One might create a user-defined type called 'gameObjectId' which is in fact a bigint. Then wherever you see a 'gameObjectId' column, it immediately reminds you of its domain. Because you will be creating a lot of stored procedures, it's worth reiterating the naming mantra. Choose names that will allow grouping stored procedures with similar functionality. Stored procedures that return the entire of contents of a table might be prefixed with the word 'Contents', such as 'ContentsCreature' which returns the entire contents of the Creature table. CharactersByUserId is understood to be a select statement that returns multiple rows of character related data given a UserId as an argument. You can overcome the character limit on names in some databases by establishing a consistent set of abbreviations that everyone is able to reference. A stored procedure that you would like to name RetrieveItemsByInventoryCategory could be called RtrvItmByInvCtg and still meet the naming goals. Tables Almost without exception, each table in the database should be created with a key. A key is one or more columns that can be used to uniquely identify a single row on a table. If at all possible, have keys be system assigned. This allows the database to generate the next unique value for a new row when created on its primary table. To illustrate: the Creature table is given a primary key called CreatureId that is system assigned. When the Bugbear creature is added to the Creature table, it is assigned the value 15 for its CreatureId. Then whenever a creature has a CreatureId value of 15 in the game, it is guaranteed to be the Bugbear creature. It is also useful to retain the name of a primary key column when it appears as a foreign key on another table. Applied consistently, one can be assured that the CreatureId column on the CreatureSpawner table represents a creature associated with a particular spawner. If the value is 15, it further means that a Bugbear is associated with the spawner identified by the given SpawnerId. Keys with numerical data types are preferable because they make for a more efficient index compared to other data types. Additionally, no one really cares that the value 15 represents a Bugbear, but one might need to change 'Bugbear' to 'BuggBear' for some reason, and a changing value violates the purpose of a key. When picking the numerical data type for a system assigned key, pick one that is large enough that it will never overflow in the lifetime of the game. Assume dramatic growth for the game beyond your wildest dreams (hey, why not dream?), and assign the data type accordingly, even if it feels like space is being wasted. Disk space is cheap compared to reworking a system that does not adequately anticipate growth. Creating a 'smart' key is never acceptable. A smart key is present when one is able to look at its value alone and determine things about the row it is identifying. The world abounds with smart keys, and the associated expense of dealing with changes, as growth can no longer be accommodated. The classic example is the Vehicle Identification Number (VIN) that identifies each vehicle. Various positions in the VIN represent different attributes, such as country of origin. Because the VIN is a smart key, the IT departments at automakers and state transportation agencies have suffered whenever expansion was required to accommodate new requirements. Don't bring the headache of smart keys into your game; use system assigned keys instead. When a table has a key with multiples columns they should be ordered based on their cardinality value from high to low. This allows for the creation of a more effective index structure in the database. Cardinality is the number of unique values in the domain of a particular column. For example, a flag has a cardinality of 2. It can be 0 or 1. Other cardinalities may not be so obvious. While you may choose a 32-bit integer for the SpawnerId, its cardinality is only the actual number of unique spawners in the game. Typically though, the ordering of columns will be easy to determine, based on familiarity with the system requirements and how the table will be utilized. There is one caution when using system assigned keys. If the value of a key has to be synchronized across multiple database instances, be wary of the possibility of undesirable results. You may need to plan for a centralized assigner mechanism shared by each database instance, or the partitioning of the number space from which assignments may occur. For example, if every user has a unique identifier across the game universe, that identifier must be centrally allocated, or each database instance must make provision for allocating identifiers from a pre-defined and non-overlapping range. It is enormously helpful to interact with the database in terms of related groups of tables, such as all the tables supporting a particular game system. MS SQL Server has a great tool that provides this feature. The Database Diagram, a database object that can be manipulated from Enterprise Manager allows WYSIYG table creation, modification and association to other tables. Multiple diagrams can exist in a database instance, and can contain any combinations of tables. This is superb for partitioning related tables into logical groups from a game system perspective, and is one tool I would hate to do without! Tables Indexes The general approach to creating indexes on tables should be "wait and see if it's necessary". It is simple to add an index when it becomes obvious it is needed; it's much harder to anticipate that one is going to be needed until the table is utilized and a particular query performs poorly because an index is missing. Since the maintenance of additional indexes on a table can impact performance, only use them when there is an obviously verifiable need. I usually only create the index associated with the primary key of a table. I will consider an additional index if I know for a fact that there will be a query that is not going to utilize the primary key to access the table, and that there will be enough data in the table to slow down performance. Databases usually store data with some uniform page size for efficient access. This page size is typically large enough to contain the entire contents of small tables with a few rows, allowing them to be retrieved with a single read. Before adding an index, do some homework to verify that the reason you are having poor performance of a query is truly the reason. Just as a profiler reveals hotspots in code, almost all databases provide the ability to analyze the execution plan of a query. Use this mechanism to profile your queries, both before any modifications to understand what needs to be done, and after the modifications to see that they had the intended effect. Schema Structure Relational tables can be designed to meet an idealized structure that has no redundancy and maximum flexibility in handling changes to the system. However, in typical database applications, this structure is often compromised to achieve performance goals. The act of re-factoring the 'normalized' structure of a database to speed data access and manipulation is known as 'de-normalization'. De-normalization can be a black art, and the gyrations required to achieve particular performance targets while balancing ease of use is not for the faint of heart. The following is my approach to de-normalization, and it goes against conventional wisdom. Don't do it. Admittedly there are some caveats to this perspective, so let me explain. Unless you are in a position to throw ever-bigger hardware at database performance issues, you will be spending development resources on an implementation that alleviates the need to block processing when accessing data from the database. There is little question that to get sufficient performance in the game loop, you will have to implement some combination of either a data caching scheme, an asynchronous data access mechanism or a separate data access thread. By isolating the game loop from sensitivity to data access time, the requirement to optimize data access speed through de-normalization is significantly reduced. Moreover, if the game design can be 'nudged' into embracing a few guidelines, then we are free to maintain a normalized data structure in the schema to support the game. The following are these guidelines.
|
|||||||||||||||||||||||||||||||||||||||||||||||||
|
|
Introduction
Resource
Guide 
