A Data Model for Map- and Turn-Based Games

Turn-based games and their map-based counterparts have been popular for a long time. Suppose we were to take such games into the 21st century by making them digital. What data model is up to the job?

If you think about it, many popular games have been map-based, turn-based, or both. On the one hand, we have simple low-tech board games like Don’t Get Angry and Monopoly; the other end of the spectrum includes the computer-based Panzer General and Civilization series as well as the stand-alone Alpha Centauri game. Others, like Settlers of Catan or Scythe, exist in the middle, as they are relatively complex board games. (Although we should note that Settlers has several digital spinoffs and Scythe is scheduled to debut one soon.)

Most people enjoy playing such games, either casually or competitively. What kind of data model should we use to manage turn- and map-based games?

What Do We Need to Know About Turn-Based Games?

All map-based games have different goals and gameplay styles, but they do have some key similarities. They are all turn-based, they all use a hexagonal grid map, and they all include units or structures that can be placed on the map.

In this model, we won’t cover any specific games, but we will cover most of the expected game mechanics. Our database will be able to support the following requirements:

• Games will be browser-based.
• Games will be multiplayer.
• For each new match, we’ll generate a new map (i.e. by shuffling the order of the tiles).
• Different types of tiles allow different upgrades and provide different bonuses.
• In-game resources can be used to upgrade tiles and produce units.
• Units will be able to move, attack a tile, or defend a tile.

Some of these requirements are also the requirements for most of the above-mentioned games. However, some games may lack one or two, while others have a few extra. But as I said, we’ll be creating a completely new game, or at least a good backbone for a really sophisticated project.

If you’re a regular reader of our blog, you’ll know that we’ve already discussed some game-related data models. You can see them here:

• A Database Model for Simple Board Games
• MMO Games and Database Design
• A Database Model for Action Games
• Build Your Own City: An Open-Ended Building Game Data Model

Let’s move on to the model.

The Data Model

The data model consists of four subject areas:

• Maps & resources
• Upgrades & units
• Game setup
• Gameplay

We’ll present each subject area in the order it is listed.

Section 1: Maps and Resources

I’ll start with the Maps & resources subject area. It holds all the tables we’ll need as a prerequisite to map generation.

Each map is generated from a set of predefined tiles. We’ll have several kinds of “tile_type” and each type will have different properties. For each type, we’ll store only the UNIQUE “tile_type_name”. Some expected values include “forest”, “sea” and “desert”.

Resources are another important requirement for most games. They are something players “spend” to make things happen in the game. Some of the most common resources are “energy”, “food”, “gold”, “ore”, and “wood”. We’ll store a complete list of resources in the “resource” dictionary. Once more, this table will contain only the UNIQUE “resource_name”.

Each tile type can by default produce a certain quantity of resources (one or more) per turn. All such quantities are kept in the “generates” table. We’ll store the UNIQUE pair “tile_type_id” – “resource_id” and the “quantity” that is produced on that tile.

The next thing we need to specify is how tiles will be placed on the map. To do so, we’ll first need to know the “map_size”. This simple dictionary contains a list of UNIQUE “map_size_name” attributes with expected values of “S”, “M”, “L”, “XL”, and “XXL”.

Now we’ll describe all possible varieties of “map_type” we’ll have in our game. Players will be able to choose a map type when they are starting a new game. These map types encompass the map size and its tile positions. For each record in this table, we’ll store:

• “map_type_name” – UNIQUELY denotes that map type.
• “map_size_id” – References the “map_size” dictionary.
• “players_min” and “players_max” – The minimum and maximum number of players allowed on that map.

For each map type, we’ll decide how many of each tile type will be placed on that map. Note that we won’t be stating tiles’ positions on the map; that will be handled later, in the map randomization process. For each UNIQUE combination of “map_type_id” – “tile_type_id”, we’ll store the “quantity” of that tile type allowed on that map.

Now let’s discuss how we’ll handle the positions of various tiles.

First, we need to list of all possible positions in any game. Therefore, we’ll store notations for all positions on the largest map type. We’ll do that using the “position” dictionary. The only attribute here, besides the primary key attribute, is the “position” column. In this column, we’ll store a UNIQUE combination used to denote that position. This could be position coordinates, e.g. [1:1].

Now we’ll need to define all adjacent positions for all tiles. We could probably just use coordinates, but I want to delineate this at the database level. One reason is that some positions may be related in a special manner (e.g. edges of the world). The “adjacent_position” table contains a list of all adjacent tile pairs. These positions will be used to find adjacent positions when we want to move the unit from one tile to another or if we want to build a structure on multiple tiles. In case an adjacent tile is not part of the map, we’ll have that info in the related “map_size_id”.

The last thing we’ll do in this subject area is to store details for every “tile_position” that will be used on that map type. For each map type, we’ll have a set of positions where tiles will be randomly placed. To do so, we’ll store a list of UNIQUE pairs of “map_type_id” – “position_id”.

Section 2: Upgrades and Units

Before we can start our game, we still need to set up its Upgrades & units. Upgrades are directly related to tiles and will generate resources; units have some additional properties that will allow the player to move them about or use them to perform an in-game action.

Let’s start with the upgrades, which are all stored in the “upgrade” table. Upgrades will mostly be structures that we can create on certain types of tiles. These structures will provide either resources or bonuses. Resources are generated every turn; bonuses are given when the player defends the upgraded tile or uses its structure(s) in an attack. For each update type, we’ll store:

• “upgrade_name” – UNIQUELY identifies that upgrade.
• “attack_bonus”, “defense_bonus”, and “movement_bonus” – Numerically define the bonuses received when that upgrade is applied to a tile. If there is no specific bonus, these attributes would contain a value of zero.

We can expect that certain upgrades will be limited to certain tile types. In order to manage these dependencies, we’ll store a list of all such UNIQUE “upgrade_id” – “tile_type_id” pairs in the “upgrade” table.

The last two things we need to assign for upgrades are how many resources it requires and how many resources it will generate. We’ll use almost the same structure for these two tables, “upgrade_cost” and “production”. In each table, we’ll store the UNIQUE “upgrade_id” – “resource_id” pair and the formula per level for each resource (the “upgrade_cost_formula” and the “production_formula”). While upgrade cost is a one-time thing, the values stored in the “production” table denote resource production per turn.

The remaining three tables in this subject area describe the units we’ll use in the game.

First, we’ll list all possible types of units. These could be segmented by the medium they move through – e.g. land unit, air unit, or naval unit. We can further define them by their role – transport, attack, or defense. The complete list is stored in the “unit_category” dictionary, which contains only one UNIQUE value, “category_name”.

The “unit_type” table will store a complete list of all the unit types. Units are used to move across the map, build structures, and fight other players. For each type, we’ll store the following details:

• “type_name” – A UNIQUE unit type name.
• “unit_category_id” – References the “unit_category” dictionary.
• “description” – A text description of that unit type.
• “properties” – All properties of that unit type, stored as text in predefined notation, e.g. “attack:1”, “defense:1”, “movement:3”, etc.

The final table in this subject area is the “unit_cost” table. It handles the resource cost per unit. For each UNIQUE “unit_id” – “resource_id” pair, we’ll store the “unit_cost_formula” that calculates the resource cost per level of that unit. This is important when the unit can be upgraded.

Section 3: Game Setup

In the Game setup area, we’ll store info about players, game instances, and any additional details related to participants and maps.

Let’s start with the “player” table. It’s a standard user-login table. It stores a UNIQUE “user_name” and the hash value of the “password” that the user has chosen. The “nickname” attribute stores the user’s screen name. This must be UNIQUE. The value of the “email” attribute also must be UNIQUE, since users can have only one account per email address. The “confirmation_code” is the value sent to the user during the registration process, and the “confirmation_date” is the time when the user confirmed their email.

It only takes one player to initiate new “game_instance”. They’ll select a map type and invite other players to join them. For each new game instance, we’ll store the time when it was created, the ID of the player that initiated it, and the game description, if any.

A list of all participants in a game is stored in the “participant” table. For each UNIQUE “game_instance_id” – “player_id” pair, we’ll store the time when each joined that game instance and the “score” they achieved. The score will always be defined, even when the starting score is zero.

Each player will have a certain amount of resources available at the start of each turn and at the start of the game. For each UNIQUE combination of “participant_id” – “resource_id”, we’ll store the “quantity” of that resource available to that player at that time. We’ll use the “resources_current” table to manage this.

Each game instance will have a related “map”. The player that initiates the game instance will choose the map type, and that type will be related to that game instance. Therefore, we’ll store UNIQUE pairs of “game_instance_id” – “map_type_id” attributes in this table. Since a game instance can have only one map and vice versa, this is a one-to-one relation. We could store all the values from the “map” table in the “game_instance” table, but I want to keep them separate.

Finally, we come to the “tile” table. This is where we’ll manage the placement of all tiles on a map. For each map position, one tile type will be randomly selected and stored in this table. We’ll need relations to the map, the position used, and the tile type here. We’ll also store the ID of the participant that “owns” each tile.

Section 4: Gameplay

The last subject area in our model is Gameplay. This is where we’ll store all the actions and interactions that happen in the game.

The central table in this subject area is the “action” table. For each action, we’ll record the related game instance (“game_instance_id”), the player that made it (“participant_id”), the type of action (“action_type_id”), the notation used to describe that action (“action_notation”), and when that action was made (“ts”).

We’ve already mentioned “action_type” dictionary. This dictionary stores a list of UNIQUE “action_type_name” attributes, like “build”, “move”, “attack”, and “defend”.

Actions can be related to units, tiles, or both. Before we start relating actions and units, we’ll first define all the units produced and used during the game. So far, we’ve only defined a list of possible unit types. A player could build many units of the same type, so we need to be able to store all of them. We’ll do that using the “unit” table. For each unit, we’ll have:

• “unit_type_id” – The “unit type”.
• “starting_tile_id” – The tile where the unit was initially produced or placed.
• “current_tile_id” – The tile where the unit is currently placed (or was last placed before it was destroyed).
• “current_status” – The current status of the unit (in a given notation).
• “active” – If the unit is currently active on the map. (It could have been destroyed or disbanded.)

We’ll record all units involved in a certain action in the “unit_in_action” table. All we’ll store here is a UNIQUE pair denoting the relevant units and actions.

The final table in this subject area and our model is the “tile_in_action” table. For each action, we’ll list all the tiles that were affected by it, e.g. if units moved across a few tiles. We’ll store the ID of the related action and tiles and the order of the interaction. Please note that the “action_id” – “tile_id” pair is NOT UNIQUE because a unit can move across the same tile more than once during an action.

What Would You Add to This Model?

As I originally stated, I wanted to create the backbone for a turn- and map-based game that would be based on common game elements. This model isn’t complete; it doesn’t cover some functionalities, including:

• We can’t generate maps with a completely randomized number of tiles.
• We have no provision to create fixed tiles, e.g. on a certain map type, let tile [1:1] always be a forest.
• We’d need more modifications to play games like Settlers of Catan. In this game, the points and sides of the hexagon are also part of the map.
• The game’s resources are defined on the player level. We have to make modifications if we want to relate resources to specific tiles.

This model is a good start on a more complex model that would enable us to play turn-based and map-based games. It’s general, so feel free to post your own suggestions in the comments. A good way to start is to think of a computer game or board game you like and see if this model can be used for that game. If not, what changes are needed?