This article describes the layout (schema) of a database I am designing to help me run a wargame campaign. Readers who are also campaign "managers" may be interested in the thinking I went through in coming up with it.

This is a miniatures campaign, using mostly 25mm figures. It is set in the late Middle Ages (or early Renaissance) in northern Italy. The map covers the area from Arona east to Venice, and from Trent south to La Spezia. There are a total of 26 cities on the map, connected by roads. Off-road movement is possible, but is at a much reduced rate.

There are two sides in the game, the "French" and the "Imperialists", each represented by three "tyrants". The game ends when one of the two sides controls 18 cities at the end of a 6-turn game-year. At that time, the player on the winning side who controls the greatest number of cities is the winner of the game. Players on the same side are forbidden to directly attack one another, but can take and garrison each other's cities.

Until now, I have been maintaining my campaign paperwork on my Macintosh, but only as a set of Word documents. In an effort to automate my system (and, frankly, because it seemed like a fun project) I decided to make a Microsoft Access 2.0 application database to support the campaign. The particulars of the paperwork I have to maintain involve the following:

1. Each player runs one city-state. Players tend to drop out of the game (probably due to me talents as a referee!); therefore several players may, over time, run the same city-state. I need to track which player controls which city-state.

2. Each city-state is a collection of cities. Cities are controlled either by direct conquest and garrison, or by "faction points". Players may lend their faction points in a city to another player. I need to track faction point totals for each player in each city. I also need to keep track of which cities are controlled by which players, and whether this is by faction points or conquest and garrison.

3. Each city has a tax value and a supply value (how many figures it can supply for free each turn. Players may pillage cities, reducing their tax and supply values. I need to keep track of the current tax and supply value of each city.

4. Each player has one or more forces on the map. A force can have a variety of troop types. Some of the troops in a force may be paid or unpaid, supplied or unsullied. During a turn, the contents of a force may change quite a bit. I need to rack the location and contents of each force, whether the force as a whole should be treated as unpaid and/or unsullied, and which leaders are present in the force. I also need to be able to make notes concerning the force for my reference, or to give to the players (e.g. "Your scouts are getting rumors of an enemy army somewhere to the north"). For siege and assault purposes I also need to keep track of the total point value of each force, and how many "scouting points" in has.

5. Each player also possesses one or more leaders (Commanders-in-Chief and Subgenerals). Each leader has a seniority (the order in which it is bought) and a skill level (A, B, C or D). Skill levels can change. I need to track the location and current skill level of each general for each player, each turn.

6. Players also have spies and counterspies, to give them information about enemy movements. I need to know which players have spies or counterspies in which locations, and their performance for the turn.

7. Finally, each player has a victory point total which I must track from turn to turn.

Decisions, Decisions

The first decision I had to make was: what tables should the database have?

Currently, I have a Word ® document which tracks the forces of each player, turn by turn. The document is simply a set of tables, one per player, divided into cells. There is one document per turn, with a table in it for each player. The turns for each game year are grouped into a single directory. This seemed like the obvious first choice to transfer to a database table, since it is the heart of the system (such as it is).

My first thought was to make the database table look just like the Word Document tables. I would have a column with the ID number of the force, the player's name, the current contents of the force, and any notes relevant to the force.

Well, that scheme began to fall apart when it hit some brick walls, such as: What happens when a force moves to a new location? How do I keep track of the turns? How do I keep track of the troops in a force in such a way that the computer can count the figures and calculate their total point value (an annoying chore that I now do by hand)? What happens when a player drops out, and another player takes his place?

Player ID Name Address Phone E-mail Nation Active? Notes

The active flag is reset if a player drops out. This would let me keep stale names in the table for future use.

Thus my first table turned out to be more basic than the "force location" table; but I still had to consider what this most important table would look like.

I decided that the Force Location Table would have to break out each troop type in each force into a separate record. A record would have to answer the following questions:

1) What game year and turn of the game is it? 2) What is the ID of the force? 3) Who does it belong to? 4) Where is it located? 5) What troop types are in the force? 6) How many such troops are there? 7) Are they in supply? 8) Are they paid? 9) Are there any notes relevant to these troops?

Starting

After some head-scratching, I decided to start with the players, I decided that each Player would have a numeric ID, rather than a name, with a Player Table linking each Player ID with the name, address, phone number, e-mail address, and nation/state of each player. This way, if a player changed, the ID could stay the same (no need to go around changing all sorts of other tables).

The Player ID would be the key of this table. In a relational database, as most of you probably already know, each record must have a unique "key" to identify it. In a simple table such as this, the key is usually just one field; in more complicated tables, the key may be composed of a number of fields. In combined-field keys, each field comprising the key need not be unique, as long as all the fields concatenated in the required order yield a unique key.

In the table layouts below, any "ID" field is a long integer, a "global" ID is unique across the entire table, and a field ending in a "T' is a yes/no "flag" type field.

Each record in the player table (tblPlayers) would thus look like this (the bold field is the key):

I decided that there would be three interconnected tables to define this information: A force table, a force contents table, and a troop type table.

Each record in the force table would give global data for each force. It would have a one-to-many relation to the force contents table. This table would list the different troop types current for each force. The troop type table would define the parameters of each troop type in the game. There would be a oneto-many relation from the troop table to the force contents table.

A one-to-many relation, by the way, states that each record in first table can have a link to many records in the second table. For example, one basic force record can point to many force contents records, and one troop type record can also point to many force contents table records.

The layouts of the tables would thus be as follows:

Force Table:

"Global" Force ID	Game Year	Turn	Owning Player ID	Force player ID	Location ID	Notes

The "Player force ID" would be the ID number of the force's counter on the map; this could not be a global ID because several players could easily have forces with the same ID number. Note that I could have used Player ID + Player Force ID as the key, if

Force Contents Table:

Parent "Global" Force ID	"Global" Record ID	Troop Type ID	Troop Count	Paid?	Supplied?	Arrears

"Arrears" is the number of game-years since the troops were paid; it is only meaningful if the troops are unpaid.

Troop Type Table:

"Global" Troop Type ID	Description	Point Value	Cost to Buy (per figure)	Cost to Pay (per figure)	Notes

Next, I had to consider the cities in the game, and how to track their current status and who controlled each.

I decided that there would be, first of all, a Cities table, listing data about each city which would change seldom if ever: The name, location, base tax value, etc.

Next, I would have a Pillage table, listing whether the city had been pillaged, how many times, whether brigandage would further reduce its tax value, etc.

Cities Table:

"Global" City ID	Name	Location ID	Base Tax Value	Base Supply Value	Defense Value	Notes

Pillage Table:

GameYear	Parent Global City ID	Times Pillaged	Brigandage?	Damage from Brigands	Base Supply Value	Defense Value	Notes

I would also have a City Loyalties table, listing who each city was currently loyal to and controlled by (not necessarily the same player!).

City Loyalty Table:

GameYear	Turn	Parent Global City ID	Loyal To Player ID	Controlled by Player ID

Finally, I would have a Faction Point table listing, for each turn and city, the faction points for each player. The table layouts would then be as follows:

Faction Point Table:

GameYear	Turn	Parent Global Player ID	Parent Global City ID	Current Faction Point Total	Notes

I had also added in the game-year and the turn. However, a four-field key is really rather clumsy; I decided that a single global ID would serve more cleanly as the table key.

Note that this table has a four-field key, which contradicts my earlier statement about clumsy keys. I decided to do this here because this would be a handy way of stopping accidental duplicate records from being entered. When I make a design rule, I stick with it only until I decide to change my mind!

I decided to have a one-to-many relation from the City table to the City Loyalty table and to the Pillage table. I decided to let the Faction Point table stand alone -- at least for the moment.

Lastly, I turned my thoughts to leaders, spies, and victory points. I decided to have a one-to-many relation from the player table to each of these tables. These tables were pretty simple to think up, so I won't go into a lot of detail about them.

Leader Table:

GameYear	Turn	Parent Player ID	Leader ID	Type (CiC or SubGen.)	Name	Skill Level	Notes

Spy Table:

GameYear	Turn	Parent Player ID	Spy ID	Type (spy or Ctr-spy.)	Name	Skill Level	Notes

Victory Point Table:

GameYear	Turn	Parent Player ID	Victory Poiunt Total

So there it is. The next step in my design is to come up with the forms I want to use. That is, what will I actually want to see when I use the database application? I will send in an article on this subject when I get the forms designed.

Here are the layouts I came up with (screen shot of the database layout (Microsoft Access ® 2.0 format):