Build Your Own City: An Open-Ended Building Game Data Model

City-building and management games have been very popular for decades. If you were to build your own city management game, what kind of data model would you need to run it? Let’s find out.

With the advent of SimCity in 1989, gamers could experience a new type of simulation game, one that allowed them to plan and control an entire city. SimCity was designed by Will Wright and initially released for Macintosh computers; now you can play variants of this game on almost any device you own. Incidentally, there were other building management games before SimCity, but gameplay and building space were limited.

If you’ve ever wondered what data model could run an open-ended building game, you’re in luck. That’s the focus of today’s article. Sit back, relax, and prepare to learn about running a sim game.

What Should We Know About City-Building Games?

Before moving to the data model, let’s consider some questions specific to this genre:

1. What is the main idea behind our game?

   The main idea is to have an open-ended game that would provide players with enough flexibility to expand and improve their cities, islands, kingdoms, etc. The game could include predefined maps, but it should also allow players to generate their own maps or even expand maps as they advance in the game. When players come to the end of the original map, the game engine will automatically add new tiles to that map. This will make the game almost completely unlimited.

   I mentioned SimCity in the introduction, but our model should support more than just city-building games. Other similar strategy-building games where players improve their environment should also be possible.

2. Which game mechanics should be included in the data model?

   Most game mechanics will be on the game’s front end, but we will store some rules in the database. We’ll store all objects that are used (or could be used) in the game, plus the dependencies and prerequisites needed to build new objects or make improvements. Some improvements could be prerequisites for other improvements. For example, players need to have a steel company to make steel constructions.

   We can expect that our game will start with a generated terrain, which will be the most important factor in influencing gameplay. (The terrain will dictate a lot of players’ actions. Obviously, players won’t build a railroad on a deep sea tile, for example.)

3. Single player or multiplayer? Online or offline?

   The database is designed so it will cover a multiplayer online game that can be played using just a browser. Still, everything is organized so that the game could be played in an offline single-player mode.

   I assume that we’ll generate one map for each game instance. For each map, we’ll generate a terrain and enable players to choose that map and play on it. Think of the maps as different universes, which are common in many browser games.

The Data Model

The data model consists of three main subject areas:

• Map details
• Improvements
• Player actions

There is also one stand-alone table, the constructed table. I’ll describe all three subject areas in the order they are listed.

Section 1: Map Details

The Map details subject area consists of five tables that describe all map details. These details can either be predefined or generated as needed. The game map is used as a board where users will compete against each other or play a solo game. In the case of a multiplayer game, a group of users would connect to the same map and play, either in real-time or in turn-based style.

The central table here is the map table. It contains only two items: the UNIQUE user- or randomly-generated map names and the time the map was created. How maps are randomly generated (presumably by an algorithm, with user inputs) is outside the scope of this article.

The terrain is composed of tiles. Each tile will have a position defined inside the map, descriptive properties (i.e. “ocean”, “mountain”), and an owner. This information will be used to allow or disallow certain actions on that tile.

The terrain_type dictionary is used to store the general classification of the terrain. The terrain_type_name attribute will contain only UNIQUE values like: “sea”, “river”, “forest”, “mountain”, “field”, etc.

More specific terrain classification is stored in the terrain table. It has only one UNIQUE attribute, terrain_name. If we were describing the “sea” terrain type, we could expect to see descriptions like “shallow water” or “deep sea” here.

Each tile will have its own unique coordinates on a map, which are stored in the coordinates dictionary. We will start by assigning coordinates for each tile. When a player reaches the end of the map and adds new tiles to that map, we’ll add new coordinates to this table. However, we will store a list of all coordinates of all tiles in all maps in a different table.

Besides the primary key attribute, the coordinates table contains only the x, y and z coordinate values. The first two coordinates are mandatory, while the z coordinate is arbitrary. This is because our game could be a 2D or a 3D game. These three coordinates together form the UNIQUE key of the table.

The tile table describes a single tile on a map. It relates data from all four previously-mentioned tables using foreign keys and contains only one attribute that is specific to this table. The attributes in this table are:

• map_id – The ID of the map the tile belongs to.
• coordinates_id – The ID that points to the x, y, and z coordinates of that tile.
• terrain_id – Denotes the terrain type of that tile.
• owner_id – The ID of the player (in that game instance) who currently owns that tile.
• tile_description – Stores all specific information related to that tile. For example, if a tile is of a deep-sea terrain type, we could store the sea depth in this attribute.

Section 2: Improvements

The second subject area in our model is the Improvements area. It has four tables that describe all possible improvements players could make on a terrain or on the structures built on it.

Note: The “terrain” table is a copy of the table from the previous subject area. It is used here in order to avoid overlapping and thus simplify the model.

The central table in this subject area is the improvement table. Here we’ll store a list of all improvements that could be done on a map. These could be related with terrain, e.g. farming, drilling, mining, etc. Besides that, this table will also hold a list of structures we could build on a terrain. For each improvement, we’ll define:

• improvement_type_id – References the improvement_type dictionary and denotes the general nature of the improvement.
• level_construction_formula, level_cost_formula, level_income_formula – All store formulas as text attributes. We’ll parse these fields and use the formulas to calculate construction cost, maintenance cost, or income per turn (based on the value of the current_level attribute in the constructed table). Note that this model doesn’t have the complex structure needed to handle different types of resources. I assume that we’ll have one resource to make improvements and to cover maintenance costs. Also, structures (improvements) could provide an income. We’ll store current funds and income per turn in the play table.
• tiles_x, tiles_y and tiles_z – Are the dimensions required to perform this improvement. These three numbers are the number of unoccupied tiles in each direction, starting from the tile the player is currently pointing at. The tiles_z attribute could contain NULL values if the game has a 2D map.

We have already referenced the improvement_type dictionary. This dictionary will contain a set of UNIQUE improvement type names that should describe and group improvements. Some expected values in this table are “road”, “bridge” and “factory”.

The remaining two tables in this subject area are used to store all prerequisites for improvements. We can expect two types of prerequisites – terrain type, and other existing improvements. An example of the terrain prerequisite is that a tile should contain earth if we want to construct a building on it. On the other hand, some structures may need other structures as a prerequisite. For example, if we want to build a railway station on a tile, we should have railway tracks already laid down on that tile. We can expect that some tiles will also have a combination of terrain and infrastructure prerequisites. In our model, we’ll use two tables to store these prerequisites.

The terrain_prerequisite contains the UNIQUE improvement_id – terrain_id pair and an ID field. If one improvement has many records in this table, it means that only one terrain is needed to build that improvement – e.g. you could build a bridge over a mountain pass, a river, or even the sea. We can look on these prerequisites as an XOR condition. The terrain of the current tile should be of a type that is assigned to that improvement.

Unlike the terrain prerequisites, prerequisites relating to existing structures can be treated as AND conditions. Basically, if there are one or more prerequisites for an improvement, all of them should be built before players can make the improvement. All such prerequisites are stored in the improvement_prerequisite table. This table also contains the UNIQUE improvement_id – prerequisite_id pair.

Section 3: Player Actions

The Player actions subject area introduces five tables that store actions made by players during the game. Two tables, the map table and the tile table, are copies used here in order to simplify the model. The constructed table was already mentioned, but we haven’t described it yet. We’ll do that here because the data stored in the constructed table is closely related to data in the action table.

We’ll store a list of all players who have played on any map in our game in the player table. A player will have one account that could be used to play on any map. This table will store general player details that will be the same no matter which map is being used. The attributes in the table are:

• user_name and password – A player’s login details. A username can contain only UNIQUE values.
• nickname – The player’s screen name, which must also be a UNIQUE value.
• email – The email the player used during the registration process. It also has to be UNIQUE.
• confirmation_code and confirmation_date – Details related to the email confirmation process.

The play table relates players with specific maps they’ve decided to play. Just think of each map as a new game instance with slightly different parameters. Maybe players want to play some maps simultaneously, e.g. a few turns on one map, a few on another, etc. For each record in this table, we’ll store:

• player_id – The ID of the player that plays the referenced map.
• map_id – The ID of the map the player has chosen.
• joined_at – When the player started playing that map.
• current_funds – The amount of resources a player currently has for this map. These enable a player to perform game actions. This value is updated each time a player makes an action associated with a cost.
• income_per_turn – The player’s per-turn resource income on that map. In case of a turn-based game, this value is added to the current_funds at the end of each turn. If a game is a real-time simulation, this value will be added at regular intervals. The income_per_turn will be updated each time a player constructs an improvement that has a new cost or income per turn.

The player_id – map_id pair is the UNIQUE key of this table. A player can play a map only once.

The constructed table relates players and tiles with improvements and actions. This makes it very important to the model. Data is inserted and updated according to actions the player has made during the game. Each time a player constructs something, we’ll also relate this record with the record in the action table. For each record, we’ll store:

• improvement_id – The ID of the improvement constructed. There can be several improvements of the same type (e.g. upgrading a building) on the same tile. It is important to note that only the last improvement will be active.
• tile_id – The ID of the map tile where the improvement was built. If the improvement has dimensions that are greater than 1 tile in any direction, we should add one record for each tile. E.g. if tiles_x = 2 and tiles_y = 3, we’ll insert 6 records, one for each tile, starting from the tile that the player defined as the starting tile.
• play_id – References the play table and tells us which player (in that game instance) made that improvement.
• constructed_at – The timestamp when the improvement was made.
• deconstructed_at – The timestamp when the improvement was deconstructed. This could happen if the player wants to remove a current structure to build another in its place. This can contain a NULL value because the deconstruction may never occur.
• upgraded_at – A timestamp when the upgrade was made.
• current_level – The current level of that improvement. We’ll calculate current costs and income related to that improvement based on this value, using the data from the improvement table. After the improvement is constructed, upgraded, or deconstructed, we’ll recalculate the value of the play.income_per_turn attribute.
• is_active – If an improvement is currently active on the map.

The remaining two tables in our model relate to a possible set of actions a player can make during a game and how these actions are shown on the map.

The action_type table is a simple dictionary containing a list of actions that could be made during a game. Actions are UNIQUELY defined by their name; we could expect names like “constructing”, “selling”, “buying”, or “renting”. We can assume that some of them will include only one player and one terrain, while others are an interaction between two players. The exact gameplay rules and logic (like checks) are all prerequisites for a fulfilled action, but they are implemented in the front end rather than the database layer.

In the action table, we’ll store a list of all actions that took place during a game. For each action, we’ll store following details:

• initiated – References the play table and stores the ID of the player (in this game instance) who initiated this action.
• participated – A reference similar to the previous one. The only difference is that this reference is used to store the ID of the records related to the player who was also affected by this action. This ID will exist only for actions that included two players – e.g. buying/selling, renting, and trading.
• action_type_id – References the action_type dictionary.
• constructed_id – A non-mandatory reference to the constructed table. If our action relates to a certain improvement on the map, we’ll store that info here. If the action was trading (for a tile), then this attribute shall contain a NULL value.
• tile_id – References the tile table and denotes the tile affected by this action. Since all actions (at least in my mind) are related with tiles, each record in this table should contain a tile_id. If an action is related with multiple tiles, we’ll have one record for each tile.

What Would You Add?

Today we’ve discussed a data model that could run an open-ended simulation game like SimCity. Such games are really complex, and we would need a lot of improvements and a complicated data model to cover all possible options.

One thing that I would personally add are different types of resources and therefore different data structures for costs and income. Do you think we could add anything else? Please tell me in the comments section and let’s improve this model together.