Let’s be honest: we all love to play games, especially on our computers. Until the Internet became widespread, most of us played computer games by ourselves, usually against AI opponents. It was fun, but as soon as you realized how the gameplay mechanics worked, the game lost most of its magic.
The development of the Internet moved games online. Now, we can play against human opponents and test our skills against theirs. No more rote gameplay!
Then massive multiplayer online (MMO) games emerged and changed everything. Thousands of players found themselves in the same game universes, competing over resources, negotiating, trading and fighting. To make such games possible, a database structure was needed that could store all the relevant information.
In this article, we will design a model that incorporates the most common elements found in MMO games. We will discuss how to use it, its limitations, and its possible improvements.
An Introduction to Data Models for MMO Games
There are plenty of very popular MMO games today, and they involve all kinds of scenarios. I will focus here on strategy games like Ogame, Travian, Sparta: War of Empires and Imperia Online. These games are more about planning, building and strategizing, and less about direct action.
MMO games are set in different universes, are visually different, and use more or less different gameplay options. Still, some ideas are the same. Players compete for locations, fight for them, and form alliance with (and against) other players. They build structures, collect resources, and research technologies. They build units (such as warriors, tanks, traders, etc.) and use them to trade with allies or to fight with opponents. All of that needs to be supported in our database.
We can think of these games as online board games with many indexed squares. Each square can have many different actions associated with it; some actions will include multiple squares – e.g. when we move units or resources from one location to another.
The database is divided into five main areas:
Players / Users
Locations and Structures
Research and Resources
The remaining seven ungrouped tables are related to units and describe unit position and in-game movements. We will look at each of these areas in much more detail, starting with Players and Alliances.
Players and Alliances
Without a doubt, players are the most important part of any game.
player table contains a list of all registered players taking part in a game instance. We will store the players’ usernames, passwords, and screen names. These will be stored in the
nickname attributes respectively.
New users will need to provide an email address during registration. A confirmation code will be generated and sent to them, which they will reply to. We will update the
confirmation_date attribute when the user verifies their email address. So, this table has three unique keys:
Each time a user logs in, a new record is added in the
login_history table. All of the attributes in this table are self-explanatory. The
logout_time is specific. It can be NULL when the user’s current session is active or when users quit the game (without logging out) due to technical problems. In the
login_data attribute, we’ll store login details like a player’s geographic location, IP address, and the device and browser they use.
Most MMO games let us cooperate with other players. One of the standard forms of player cooperation is the alliance. Players share their in-game “private data” (online status, plans, location of their cities and colonies, etc.) with others to benefit from allied actions and for the sheer fun of it.
alliance table stores basic information about game alliances. Each one has a unique
alliance_name that we’ll store. We’ll also have a field,
date_founded, that stores when the alliance was founded. If an alliance is disbanded, we’ll store that information in the
alliance_member table relates players with alliances. Players may join and leave the same alliance more than once. Because of this, the
alliance_id pair is not a unique key. We’ll keep information regarding when a player joins the alliance and when (if) they leave in the
date_to fields. The
membership_type_id attribute is a reference to the
membership_type dictionary; it stores the current level of players’ rights in the alliance.
Players’ rights in an alliance can change over time. The
actions_allowed tables together define all possible rights for alliance members. This model doesn’t allow players to define their own levels of rights in an alliance, but that could be accomplished easily enough by adding new records in the
membership_type dictionary and storing information about which alliances they are related to.
To sum up: the values stored in these tables are defined by us during the initial setup; they’ll change only if we introduce new options.
membership_history table stores all data regarding players’ roles or rights within an alliance, including the range when these rights were valid. (For example, he could have “novice” permissions for a month, and then “full membership” from that point on.) The
date_to attribute is NULLable because currently active rights have not ended yet.
membership_actions dictionary contains a list of every action players can make in an alliance. Each action has its’ own
action_name and game logic is built around these names. We can expect values like “view members list”, “view members’ statuses” and “send message” here.
membership_type dictionary contains the unique names of the action groups used in the game. The
actions_allowed table assigns actions to membership types. Each action can be assigned to a type only once. Therefore, the
membership_type pair forms the unique key for this table.
Locations and Structures
Game locations are areas where players collect resources and build structures and units. Some games have a predefined range of possible locations, while others may allow users to define their own locations.
In a 3D space, locations can be defined with [x:y:z] coordinates. If a game has a predefined range, it may not permit players to use any location out of the range [0:1000] for all three axes, so we’re limited to a 1000 * 1000 * 1000 space.
On the other hand, maybe we want to allow players to enter the exact coordinates of their new location – e.g. [1001:2073:4] – and we want the game to process it for them.
We’ll keep a list of all locations used in an instance of our game in the
location table. Each location has its own name, but the names are not unique. On the other hand, the
coordinates attribute must contain only unique values. Location coordinates are stored as text values, so we can store coordinates for 3D games as [112:72:235]. Coordinates for 2D games can be stored as <1102:98>.
In some games, locations will have a number of squares that are used to house structures or units. We’ll keep that information in the
dimension attribute, which is a text field. A dimension can be simply number of squares in a 2D or 3D grid. The
player_id attribute stores information about the current owner of that location. It can be NULL when locations are predefined and players compete to occupy them.
structure table contains a list of all structures we can build at various game locations. Structures represent improvements that allow us to produce better units, perform new types of research, produce more resources, etc. Each structure used in the game has its own unique
structure_name. Some possible
structure_name values are “farm”, “ore mine”, “solar plant”, and “research center”.
We can expect each structure to be upgraded multiple times, so we’ll also store information about its current level. Each upgrade improves structures’ output, so it produces more resources or allows us to use new features in the game. We can’t know the maximum level of upgrade in advance, so we’ll define all the level-related stuff (costs, upgrade time and production) with formulas. All formulas stored in the database are the core of the game’s mechanics, and their adjustment is crucial for game balance and gameplay in general.
That is also the case with the
upgrade_time_formula attribute. An example value for this field is “<level> * 30 min”, where <level> represents the level we want to upgrade to.
In most cases, there are requirements that must be met before players take certain actions. Maybe we need to complete a defined amount of research before we can build new structures or vice versa. We’ll store the research level needed to build structures in the
prerequisite_research table. Relationships and the structure level needed to start various researches are kept in the in the
prerequisite_structure table. In both tables, the foreign keys
structure_id are paired to form a unique key. The
level_required attribute is the only value.
These two tables,
prerequisite_structure, also form the core of the game.
For each structure, we’ll define a list of prerequisites: other structures and their minimum levels that players must have to begin building. We’ll store this data in the
structure_required table. Here,
structure_id represents the structure we want to build;
structure_required_id is a reference to the prerequisite structure(s), and
level is the level required.
structure_built table stores information about current structure levels on a given location. The
upgrade_ongoing attribute will be set only if an upgrade is currently ongoing, while the
upgrade_end_time attribute will contain a timestamp once the upgrade is complete.
structure_formula table relates structures and resources. The foreign key pair to this table forms its unique key. This table also has two text attributes containing formulas with <level> as parameter. We’ll define these formulas, one for costs and the other for resource generation, in the database. They will be similar to the
upgrade_time_formula. We need them because we must define the resources spent in building each structure. We also need to define resource production after upgrade, if structure generates any resources (i.e. ore mine will produce <level> * 20 ore per day).
Research and Resources
Research (or technologies) in games are usually requisite to the creation of other features. Without certain levels of research, new structures or unit types can’t be built. Research also can have its own requirements. One of most common is the level of a given structure, usually called a “research lab”. Or perhaps players need to complete a certain level of research before they can start new research. All of these requirements will be handled in this section. Below, we can find the data model for Research and Resources:
research table contains a list of all possible research actions in our game. It uses the same logic as the
structure table. The
research_name attribute is the table’s unique key, while the
upgrade_time_formula field contains a text representation of the research time requirements formula, with <level> as its parameter. Any resources required for upgrades are defined in the
upgrade_formula stored in the
As with structures, we’ll define the list of all other researches and their levels that must be completed before we can start another type of research. We’ll store this data in the
research_required table, where
research_id represents the desired research;
research_required_id is a reference to the prerequisite research, and
level is the level required.
Research is related to individual players, and for each player – research pair we must store a player’s current research level and any ongoing upgrade statuses. We’ll store this information using the
research_level table in the same manner that we used the
Resources like wood, ore, gems, and energy are mined or collected and used later to build structures and other improvements. We’ll store a list of all in-game resources in the
resource dictionary. The only attribute here is the
resource_name field, and it is also the unique key of the table.
To keep track of the current quantity of resources on each location, we’ll use the
resources_on_location table. Again, a foreign key pair (
location_id) forms the unique key of the table, while the
number attribute stores the current resource values.
Units and Movements
Resources are used to produce units. Units can be used to transport resources, attack other players, or in general pillaging and burning.
The list of unit types used in our game is stored in the
unit dictionary with only one value,
unit_name; that attribute is the unique key of this table. Some common game units are “swordsman”, “battlecruiser”, “griffin”, “jet fighter”, “tank”, etc.
We need to describe each unit with specific characteristics. A list of all possible characteristics is stored in the
characteristic dictionary. The
characteristic_name field contains a unique value. Values in this field could include: “attack”, “defense” and “hit points”. We will assign characteristics to units using the
unit_characteristic relation. The foreign key pair of
characteristic_id form the unique key of the table. We will use only one attribute,
value, to store the desired value.
research_unit table contains a list of all research activities that must be finished before we can start production of a given unit type. The
unit_cost table defines the resources needed to produce a single unit. Both tables have unique keys composed of the foreign key pair (
resources_id combined with
unit_id) and one value field (
And now, the fun part. Production is fun, but moving units around and taking action is even better. We’ve already introduced the
unit table, but we’ll keep it here because of how it relates with other tables.
Either units are stationed on a location or they are moving between locations. Adding the
player_id field determines who owns either the location or the group that is moving between the locations.
If units are just stationed at the given location, we’ll store that location and the number of units stationed there. To do so, we’ll use the
When units are not stationed, they’re moving around. We’ll need to store their departure point and their destination. In addition, we need to define possible actions during movements. All such actions are stored in the
movement_type dictionary. The
type_name attribute is unique while the
allows_wait attribute determines if an action allows waiting at the destination point.
We can move a single unit type, but in almost every case we’ll move many units of several different unit types. That group will share common data and we’ll store them in the
group_movement table. In this table, we’ll define the following items:
- the player that initiated that action
- the action type
- the starting point
- the destination point
arrival_timeat the destination
return_timeto the starting point
wait_timeat the destination
return_time attribute can be NULL if this is a one-way journey, and
wait_time is defined by the player. Units belonging to a group are defined by values stored in the
units_in_group table. The foreign key pair of
group_moving_id forms the unique key of the table. The number of the units of the same type within a group is defined in the
Each movement can transport resources from one location to another. Therefore, we’ll define a many-to-many relationship between the
group_movement and the
resources tables. Besides the primary and foreign keys, the
resources_in_group table contains only the
number attribute. This field stores the amount of resources players move from the starting point to their destination.
In most cases, players can call others to join their adventure. To support that we’ll use two tables:
allied_groups. One player will initiate joint action, and that will create a new record in the
allied_movement table. All groups of units that take part in an allied action are defined by values stored in the
allied_groups table. Each group can be assigned to an allied action only once, so foreign keys form the unique key of this table.
This model gives us the basic structure needed to build an MMO strategy game. It contains the most important game features: locations, structures, resources, research, and units. It also relates them, lets us define prerequisites in the database, and stores most of the game logic in the database as well.
After these tables are populated, most of the game logic is defined and we wouldn’t expect new values to be added. Almost every table has a unique key value, either a feature name or foreign key pair. Changing units’ characteristics and production/cost formulas will allow us to change the game balance in the database layer.
How would you change this model? What do you like, and what would you do differently? Tell us in the comment section!