My home page
My game page
Once upon a time...
...there was a game collector who was working on his Master’s Degree in Information Science. During his education an "opportunity" to learn Microsoft Access reared its ugly head. What follows is the tale of how he slew this beast while simultaneously getting a grade for it and creating an inventory of his game collection that lives to this day.
That game collector was me. During the past 30+ years my collection has grown to include nearly 1000 games as well as various and sundry related items, such as gaming magazines and advertisements (lovingly referred to as "ephemera" elsewhere in this publication).
Why Go To All This Trouble?
The first question everyone asks is "Why"? Why go to all the trouble of building a database for my game collection? What will be accomplished by having such a database? The primary reason for doing this Herculean task is that it is important to me to know what I have, and do not have, so that money is not wasted on duplicates. I know I own over 900 games, and do, occasionally, buy a duplicate, unaware that it is already in my collection. Also, it would be nice to be able to select appropriate games to play with friends when given a certain limitation on time or topic. If Tom calls and says, "Hey, can we get together for a game tomorrow night? I have about three hours free. Let’s play something science fiction-y. What game do you suggest?" This database lets us know all of our options.
These two factors led me to select which information to include. Also, because I work with a library, copyright and citation information for books and journals are key concerns. When citing a work, it is appropriate to list the author, publisher, title of the work and year of publication. Because games do not always list authors (some games do not even list a publisher or publication year), I do not try to list a person, but rather a game company as author for each game.
What’s in a Game?
What constitutes a game? Or, more appropriate for this tale, what EXACTLY is in this collection and how many are there? Do Advanced Squad Leader (ASL) by Avalon Hill (AH) and two of its gamettes, like Cross of Iron, and Beyond Valor, count as three games, or one? Does each role-playing module count as a separate game? Indeed, does a set of role-playing rules count as a game at all? What about all those collectable card games? How many cards are needed before it is a game? If I have seven decks set up, ready for play, do I own seven games or 3½ two-player games?
Webster’s dictionary defines a game as: "a physical or mental competition conducted according to rules with the participants in direct opposition to each other." This definition leaves some room for interpretation. The next several paragraphs will show how I dealt with these ambiguous areas. You may choose to deal with them differently. The first thing that needs changing in this definition is the word "participant". I don’t know about you, but I, for one, do not participate in games, I play them. So change "participant" to "player".
Is it a Game, a Puzzle or Two Mints in One?
One ambiguous area is puzzles and solitaire games. Should they be included or not? Neither one represents "... a competition ... with the players in direct opposition to each other." In fact, by definition, both have only one player. What is the best way to distinguish between them? For purposes of this discussion, I define a puzzle as something that has a unique solution, i.e., there is only one right answer, whereas a game does not have such a foregone conclusion. This interpretation allows me to count such solitaire games as AH's Raid on St. Nazaire and B17 while ignoring the Rubic’s Cube and other creations of that ilk. Another way to make solitaire games fit the definition is to not require that all players be human. It is a game even if one (or more) of the player’s actions are simulated (read: dictated) by a set of rules.
Chess, checkers, etc.
Let's jump to consideration of what can be called generic games: chess, checkers, backgammon, dominoes, etc. Should they be included? If I have chess sets from 42 different manufacturers (which I don’t), I still have only one game. Which one goes in the collection? How many games are contained in a standard deck of 52 playing cards? To avoid playing favorites, none of these are included! One thing these games have in common is that they cannot be copyrighted. The design of the chess pieces or faces of the cards may be copyrighted, but the game itself cannot be. So add "copyrightable" to the definition of a game.
Our role now is to look at role-playing games. If I own the rules to TSR's Dungeons and Dragons, is that a game? What about role-playing rules and a single adventure or module, do these constitute a game? Is it necessary to add props, figures, and other paraphernalia before players can have a competition? At the risk of offending some of our AD&D diehards, role-playing games are not included in my collection. The concept that applies here is "self-contained". If a game is self-contained it has everything needed to play it "in the box", with the possible exception of common household items, e.g., paper, pencil, and dice. This restriction allows me to count magazine games that come without dice, but to ignore role-playing systems, modules, miniatures' rules, and, most notably, collectable card games! Most collectable card game starter decks are for one player, so we cannot have a competition until we buy more than one. But how to deal with all those ASL gamettes? Glad you asked, read on.
Expansion and Variants
Some other terms that need definitions are "expansion" and "variant". Again, according to Webster’s dictionary, to expand means "to increase in extent, number, volume, or scope" and a variant is "one that exhibits variation from a type or norm." These two terms are used interchangeably in gaming circles, and the difference will not be taken into account for purposes of this discussion. (Let me state that I think of an expansion as something that adds components to a game, and a variant as only rules changes that are played with the components that come with the game. But that is not important now.) This category is where ASL gamettes and other things, like all those Axis & Allies add-ons, belong.
There is one last item that needs mentioning; for the purposes of this collection computer games are being ignored. My collection is composed entirely of what some folks like to call "table top" games or board games. It is too easy to have "hidden" computer games, particularly shareware, that have no tangible components. The only place it resides is on the computer disk. Due to the transitory nature of these games (if I do not like it, I can schwack it), I will not go to the trouble of counting my computer games. Also, computer games fail our "self contained" restriction. To play it requires the correct computer in addition to the disk. Anyway, my board games greatly outnumber my computer games.
Another item important to this collection is gaming magazines. A gaming magazine is defined as "any periodical that includes a game, or whose primary focus is to discuss games." With this definition, AGCA’s Game Researchers’ Notes counts as a gaming magazine, but Games magazine does not. (Personally, I think Games magazine is a misnomer. It should be called Puzzles magazine; it rarely includes "a competition between players". But I digress.)
Our new definition
Now take a look at the reworked definition of a game. (It may seem complicated, but it works quite well in practice.) A game is "a self-contained, copyrightable set of components that allows for a competition conducted according to rules with the players (be they human or simulated by rules) in direct opposition to each other and that does not require the same outcome each time it is played." THAT is what I collect!
Classification and Categorization
There is no Dewey Decimal System for Games
Now that there are criteria for which items belong in the collection and which do not, what data about those items are need to be recorded? The answer to this question depends on what is expected of the database. Obviously, the primary reason for compiling this database is to keep track of the games in the collection. For this to happen, title, publisher, and year published should be recorded. When it comes to playing the game, information such as complexity, hours needed to play, optimal number of players and genre would be nice to know. And, to make finding the game easier (not to mention taking inventory to see if any are missing), box size and shelf location are necessary.
What is it all about?
Genre is my attempt to record a subject, era, or topic for each game, just as found in library card catalogs. Genre is still evolving as more games are added to the collection. Genre has a two tiered structure. There is a primary genre and a sub-genre or subdivision for each game. The genres so far are: "War in the Air", "War on the Ground", "War at Sea", "Business/Finance", "Fantasy", "Science Fiction", "Science", "Sport Strategy", "Sport Statistical", "Railroading", and "Other". I do not like having a genre of "other", but sometimes it cannot be avoided. As games are added and more and more share the same theme, they get broken out into what are called "niche" games. More on niche games later.
Each genre is divided into subdivisions. The "War in the Air" genre is the simplest to subdivide, because airplanes have been around for only about 100 years. The subdivisions are "WWI" (mostly plane vs. plane, like Milton Bradley’s Dogfight), "WWII" (usually includes squadrons of planes and bombers), and "Modern" (jets with missiles). "War on the Ground" games are subdivided into various time periods ranging from "Ancient" to "Post-WWII" with a special subdivision called "Across the Centuries" for games like Avalon Hill’s History of the World that do not fit into any one time slot. "War at Sea" games are subdivided by the type of ships that the game focuses on, "Sailing Ships" (including oared ships, Avalon Hill’s Trireme), "Battleships" (mostly WWI battles, XTR’s Jutland), "Aircraft Carriers", "Missile Ships", and "Submarines".
The two sport genres are easy to subdivide. The sport being simulated is the subdivision. The only game in my collection that attempts to simulate more than one sport is AH’s Decathlon, so a subdivision of "Olympics" is created for it. The "Sport statistical" genre includes any game that has charts or tables based on the performance of real-life teams or athletes. "Sport strategy" includes all other sports games.
The genre of "Niche" is used to group games that may share a common theme while not fitting in any of the other genres. These games are further divided by such themes as: "Ancient Egypt" (Lowe’s Nile), "Ancient Rome" (AH’s Circus Maximus), "Mythology/Legend" (Yaquinto’s Mythology), "New World" (SPI’s Conquistador), and the "Wild & Woolly West" (Washburne’s Conestoga). Other niches are possible, however, it is usually best to avoid having a subdivision with only one game in it.
Make Believe Games
"Fantasy" games are divided into four distinct subdivisions based on the answers to a couple of questions. Is it an army based game, like AH’s Wizard’s Quest, or a character based game, like AH’s Magic Realm? Does it use magic (i.e., spells and not just pure combat)? The four combinations of answers to those two questions serve as the subdivisions: "Army with magic", "Army with no magic", "Characters with magic", "Characters with no magic". Fantasy games without magic could just as easily be classified as wargames with a "fantasy" time period, but I prefer to keep them here. Fantasy games tend to include elves, dragons, and orcs that set them apart from standard wargames.
"Science Fiction" games are almost as easy to divide. Subdivisions include "Deep Space" (for games covering more than one solar system, SPI’s Freedom in the Galaxy), "Near Space" (only one solar system, TSR’s Buck Rogers), "Planet Surface" (AH’s Starship Troopers), "Post-Holocaust", and "Survival" (TSR’s Awful Green Things from Outer Space). Here is where the rule of no subdivision with only one game gets broken. There is a subdivision under SF called "Time Travel", with the single Yaquinto game of Time War. Oh, well.
Real World Games
The tale now goes into the "Science" genre. The subdivisions for Science are "Environment/Evolution" (Eon’s Quirks), "Exploration" (AH’s Source of the Nile), "Knowledge/Trivia", and "Space Race" (Lowe’s Countdown). Most people probably avoid playing games in this genre, because they tend to be educational. I like the double whammy of fun and learning in the same box.
Two of the trickier genres are "Business & Finance", and "Railroading". Technically, Railroading is a subset of Business. The object of both games is to make money, but I have enough railroad games to justify treating them as a separate genre. Under "Business/Finance" I have such subdivisions as "Business Planning" (AH’s TV Wars), "Exploring", "Greed & Corruption" (Creative Wargames Workshop’s Junta), "Money Exchange", "Real Estate" (Parker Brother’s Monopoly), the "Stock Market" (3M’s Stocks and Bonds), and "Commerce" (Mayfair’s Die Hanse).
For Railroad related games, there are three main subdivisions and several smaller ones. The big three are connect-the-dots-and-fulfill-contracts games (Mayfair’s Empire Builder series), the draw-your-tracks-and-race games (Games Workshop’s Railway Rivals), and the buy-stock-and-build-a-rail-net games (18xx system). The smaller subdivisions include: "Card", "Title Ownership" (AH’s Rail Baron), "Operating" (AH’s C&O, B&O) and "Trivia" games. Railroad games hold a special interest for me as my other hobby is model railroading.
Finally, it is time to address the last and least useful genre, the dreaded "OTHER"! By classifying games here, nothing is said about the topic of the game, only that it does not fit any of the more well defined topics. In order to bring a little order out of this chaos, divide "Other" into several sub-others, namely: "Abstract" (Gabiel’s Othello), "Gambling", "Card", "Detective Work" (Mattel’s Lie Detector), "Dice" (Cosmic Wimpout), "Election" (Parker Brother’s Landslide), "Espionage", "Trivia" (Selchow & Righter’s Trivial Pursuit), "Lifestyles/Life in general", "Puzzle games", "Race" (Milton Bradley’s Candyland), "Survival" (Ideal’s Titanic) and "Word" (Selchow & Righter’s Scrabble). If there is a better way to group these mismatched topics into a more coherent classification, I would love to hear about it.
Setting up the Database
Tables for Table-top Games
Now for the fun part. After collecting the above information for every game in my collection, it is time to set up the tables in my database package of choice. The first table, obviously, is the "game" table. My game table consists of the title, publication year, publisher code, genre code, complexity code, hours needed to play, optimal number of players, box shape code and shelf location. Those "codes" are used to reduce the amount of typing that is needed to enter the data. Publisher code, for example, is three letters, and is actually a pointer into the Publisher table. This table has publisher name and code, and will eventually also have such information as mailing address and still-in-business indicator. The advantage of this coding scheme is that there is no need to enter "The Avalon Hill Game Company" for each of their 100+ games in my collection, simply typing "AHG" and presto-change-o, the information is recorded.
The complexity table is simply a straight translation: "1" for Kiddy games (Candyland), "2" easy, "3" medium, "4" hard, and "5" genius (ASL). The only reason to have this table at all is to be able to display the words on reports, while entering the data with a single keystroke.
The genre table is set up slightly differently. With the two level system of genre and subdivision, it would be appropriate to have two tables. This idea was rejected because the subdivisions are not the same for each genre, so a separate table for the subdivisions of each genre would be required. That solution seems a bit unwieldy, so I opt for one table with both values, and a four letter code. To make the codes understandable in-and-of themselves, the letters relate to the genre name. For example, all "War in the Air" genre codes start with WA, "Railroading" genre codes start with RR, etc. The only exception to this coding scheme is the dreaded "Other" genre. To squeeze a little more information into this code, and to force it to sort last in the table, the single letter "Z" followed by a three letter subdivision code was used. So abstract games become ZABS and election games become ZVOT (for vote).
Why be normal?
Here is where some relational database stuff is needed. What I just described, putting codes into the master table that "point" to entries in other tables, is referred to as "normalizing". Normalizing is simply a way to store the maximum amount of information with the minimum amount of typing and disk space. If it is necessary to type and store "The Milton Bradley Game Company" for each of their games, a lot of time is spent typing and the chance for typos increase. So storing this long entry once, and storing the code with each game, saves a considerable amount of typing as well as disk space. If a full report, with the long text entries, is needed, it is easy to combine the tables at print time. If a smaller report is desired, paper can be saved by printing the short but meaningful codes. Normalizing the tables gives more flexibility in reports and queries.
To do this normalization stuff, each table needs to have a "Primary Key". This key must be unique to the data that it represents. In other words, "PBG" must only appear in the record for Parker Brothers Games, and not for some other game company. This uniqueness is no problem for the genre table, publisher table, and complexity table, but can be a big problem in the master game table. It would be nice to use the title of the game as the primary key, but what if two games have the same name? If the database is limited to only a game inventory, the master game table does not need a primary key as no other table "points" to it.
Are you unique?
Problems arise with the inclusion of magazine articles and expansions and cross-references from them to the games they relate to. Both the 3M and Avalon Hill versions of Speed Circuit are in my collection; so that title appears in my database twice. If title is used as the primary key, it fails the uniqueness rule. The computer will say that Speed Circuit is not unique and therefore cannot be a primary key. There are several ways of getting around this limitation. One is to simply leave out all but one copy of each game, but then it would not be a true inventory of my game collection. And what can be done when the same title is used for two dissimilar games, for example, Avalon Hill’s classic Jutland and XTR’s more recent magazine game by the same name? An article that gives variant rules for one would not necessarily apply to the other.
Another method of handling this problem is to combine two or more fields for the primary key, say title and publication year, or title and publisher, or all three. If fields are combined like this, they would all need to be put into the "article" table to ensure that the article points to the right game. Doable, but lots of extra typing! What works better is to establish a code for each game. Avalon Hill is good in this regard as they already have such a code for use in their "Opponents Wanted" column. No need to reinvent the wheel, so use existing codes whenever possible. However, there is still a problem with the Speed Circuit and Jutland situations. The same code cannot be used for both publishers’ versions of the game.
To solve this problem make the game code six characters long. The Avalon Hill codes are used for the beginning of the code, and the publisher (or year) is added to make the code unique for each game. So the two versions of Speed Circuit become SCT3M and SCTAH. How about all those versions of Gettysburg that Avalon Hill has published over the years? GETAH does not work for them all. So use GET61 and GET64 in the code column. This method may need a little tweaking to make it fit your particular collection.
Periodically deal with Magazines
Now add in gaming magazines. Ideally, there would be three tables to cover magazines. The "master" magazine table would include title and publisher code--the same code used in the game table for publisher. The second table would list individual volume and issue numbers and issue dates. The third table would list articles in the various magazines and cross-reference the individual issues with the games discussed. This scheme will not work, however, due to a certain magazine with a game in each issue that has had at least four different publishers! Here is where normalization breaks down. To make a long story short (if that is still possible) have only two tables for magazines. One lists the magazine name, issue number, date, and publisher, the other has the article title, magazine name, issue number, and game referenced. The reason these two tables cannot be combined into one is that the number of articles varies from issue to issue. Using two separate tables allows for variability in article count.
Another "code" table is needed for article type. I want to know if the article is a strategy article for a particular game, a scenario, a variant, a replay of a game, errata or possibly the game itself (from one of those game-in-every-issue magazines.) So put in a one letter code and the computer knows how the article relates to the game. Later, questions such as these can be asked: How many scenarios have been published for ASL? In which issue of Command was XTR’s Jutland published? What are some recommended strategies for The Russian Campaign? Answers to this last question can be very useful when preparing for an upcoming tournament!
There are two bits of data or fields that help when taking physical inventory of the collection. They both relate to the physical attributes of the game package, as opposed to the game contents. These two items are box shape/size and shelf location. Having an inventory list organized by shelf location makes doing a physical inventory a snap. Box shape and size also helps with inventorying, so you know what to look for. Is that missing game in a bookcase box, a flat box, a tube or something else?
The last and trickiest items to deal with are expansions and variants. There is a separate table for them. It contains expansion name, game referenced, publisher, year published, and comments about what the expansion contains. This table looks similar to the game table, only without columns for time, players, complexity, box shape and shelf location (assume the expansion is stored in or near the game). Most expansions do not greatly change these values for a game, anyway.
Expansions and variants are more difficult to inventory than games and magazines. Most expansions are small, non-boxed components, like cards, counters, or maps. They usually get thrown in the box with the game so that they will be there when the game is played. There is no indication on the outside of the box that the extra stuff is inside! For help in inventorying these items, consult the various publishers’ catalogs to see what expansions and variants they offer, and then check the appropriate game boxes to see if it is in there. Remember, an expansion may be from a different publisher than the game it expands.
And He Lived Happily Ever After
One unplanned benefit of this exercise is that I got to relive my youth through memories of enjoyable play sessions triggered by browsing through my collection. Who says nostalgia is not what it used to be? Anyway, this tale is only one person’s attempt to bring order out of the chaos of 30+ years of collecting and playing games. I hope it gives you some insight into how to set up a database, and how to keep your game inventory. My hope is that others might learn something by reading about what I went through to get my database where it is today. If you would like a copy of my Microsoft Access tables and relationships, just E-mail me at the address below.