Games, Web Apps, Business Intelligence

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©.