Games, Web Apps, Business Intelligence

In case you're wondering how the new risk-like wargame works, I will explain some of the ways I designed it. if you haven't seen the game, it can be found here. This isn't to say that my design is the way it has to be done, or even that it's the best way. This is just the way that I decided to do it. One thing to keep in mind is that I wrote it quickly without much forethought and I did it while simultaneously working on multiple professional projects.

First, lets start with some simple MySQL. The game uses five tables, one of which is the User Accounts table from the HackWorks site. For security reasons, I won't go into detail on that table.

The other tables store data for Armies, Regions, and Games. The last table is a Game Index table that ties together User Id, Player Id, Game Id.

Let's look at the Armies table:

CREATE TABLE `armies` (
  `a_id` int(11) NOT NULL AUTO_INCREMENT,
  `a_owner` int(11) NOT NULL,
  `a_r_id` int(11) NOT NULL,
  `a_infantry` int(11) NOT NULL,
  `a_armor` int(11) NOT NULL,
  `a_air` int(11) NOT NULL,
  PRIMARY KEY (`a_id`),
  KEY `a_owner_id` (`a_owner`,`a_r_id`)
) ENGINE=MyISAM;

As I've said, this is a simple game, so armies are comprised of three units: infantry, armor, and air. Each unit has static strength that is hard-coded into the game. This can be easily changed by storing strengths in the database so they can be modified. In the table, "a_owner" refers to the Player Id and "a_r_id" is the Region Id from the Regions table.

The Armies table is initially populated at the start of the game with all starting army data from an Army text file.

Let's look at the Regions table:

CREATE TABLE IF NOT EXISTS `regions` (
  `r_id` int(11) NOT NULL AUTO_INCREMENT,
  `r_game_id` int(11) NOT NULL,
  `r_region_id` int(11) NOT NULL,
  `r_owner_id` int(11) NOT NULL,
  PRIMARY KEY (`r_id`)
) ENGINE=MyISAM;

The regions table is populated at the start of a new game with data from a "map" text file. This table keeps track of which regions each player controls at any given time in the game. Since this game does not require you to maintain armies in every region you control, we have to keep track without the help of the Armies table. To help the players, we display a country flag in all unoccupied regions. Whenever an army moves into an enemy region, we have to update the army's location (a_r_id) in the Army table, and the region owner in the Regions table.

Next time we will discuss the Games table and the Game Index table, and how they track multiple ongoing games. Maybe we can even get into some PHP code samples then.

In the last article, we saw the 'armies' and 'regions' tables in the database. Now let's look at the 'games' and 'game_index' tables.

The 'games' table stores game-specific data for each individual game such as the current turn number and which map is used. The following MySQL code shows how the table is laid out:

CREATE TABLE IF NOT EXISTS `games` (
  `g_id` int(11) NOT NULL AUTO_INCREMENT,
  `g_status` tinyint(3) unsigned NOT NULL,
  `g_turn` int(11) unsigned NOT NULL,
  `g_phase` tinyint(3) unsigned NOT NULL,
  `g_player` int(11) unsigned NOT NULL,
  `g_max_players` int(11) unsigned NOT NULL,
  `g_army_num` int(11) NOT NULL DEFAULT '1',
  `g_reinforce` int(11) NOT NULL DEFAULT '0',
  `g_armies_init` tinyint(1) NOT NULL DEFAULT '0',
  `g_map_file` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`g_id`),
  KEY `g_current_player` (`g_player`)
) ENGINE=MyISAM;

g_status tracks when the game is awaiting players, being played, or finished. The current game phase and turn are noted in their respective fields. g_player shows which player is currently taking their turn. The maximum number of players for this game is shown in g_max_players. When the game has started and been initialized, g_init is set to '1'. The field 'g_army_num' is used to increment army names to ensure that no armies have the same name (1st Army, 2nd Armored, 3rd Air, etc). The initial value is based on the number of starting armies in the individual game (If the game begins with 20 armies, the initial field value will be 21). The map for this game is stored in g_map_file. Lastly, g_reinforce stores the number of reinforcements for the current player. Once they distribute all reinforcements for this turn, it is set to '-1' to show that the reinforcement phase is finished.

Here is a sample record from the 'games' table:

INSERT INTO `games` (`g_id`, `g_status`, `g_turn`, `g_phase`, `g_player`, `g_max_players`, `g_army_num`, `g_reinforce`, `g_armies_init`, `g_map_file`) VALUES (1, 3, 5, 1, 3, 3, 11, 4, 1, 'map001.map');

The last table used in BattleWorks is the 'game_index' table. It ties all of the other tables together. Here is what it looks like:

CREATE TABLE IF NOT EXISTS `game_index` (
  `gi_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `gi_user` int(10) unsigned NOT NULL,
  `gi_player` int(10) unsigned NOT NULL,
  `gi_game` int(10) unsigned NOT NULL,
  `gi_status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`gi_id`),
  KEY `a_user` (`gi_user`,`gi_player`,`gi_game`)
) ENGINE=MyISAM;

The fields represent the following data:
gi_user: the user id from the 'users' table
gi_player: the player id from the 'games' table
gi_game: the game id from the 'games' table
gi_status: the status of each player (active, eliminated, banned, etc.)

Now that we have the tables created, we have to populate them with our initial data. To do this we "initialize" our game through the 'MAP' and 'ARMY' classes. Our next article will show how these classes read the game data from a flat (text) file and then populate the database. These classes will also perform functions like resolving combat and calculating reinforcements, so be sure to read the next informative article in my "BattleWorks" series explaining how I wrote a browser-based wargame based on the rules of Risk©.