The badSpy Database
The badSpy Database
As you learned in Chapter 7 "Using MySQL to Create Databases," it isn't difficult to build a data table, especially if you have a tool like SQLyog. Figure 9.1 illustrates the schema of my first pass at the spy database.
Figure 9.1: The badSpy database schema looks reasonable enough.
At first glance, the design of the badSpy database seems like it ought to work, but as soon as you begin adding data to the table, you'll begin to see some problems. Figure 9.2 shows the results of the badSpy data after I started entering information about some of my field agents.
Figure 9.2: The badSpy database after I added a few agents.
Once you start entering data into the table, you'll see a few problems crop up. Look carefully at Figure 9.2 and you'll see some potential issues.
Inconsistent Data Problems
Gold Elbow's record indicates that operation Dancing Elephant is about infiltrating a suspicious zoo. Falcon's record indicates that the same operation is about infiltrating a suspicious circus. For the purpose of this example, I'm expecting that an assignment has only one description, so one of these descriptions is wrong. There's no way to know whether it's a zoo or a circus by looking at the data in the table, so both records are suspect. Likewise, it's hard to tell if operation Enduring Angst takes place in Lower Volta or Lower Votla, because the two records that describe this mission have different spellings. The circus/zoo inconsistency and the Volta/Votla problem share a common cause. In both cases the data entry person (probably a low-ranking civil servant, because international spy masters are far too busy to do their own data entry) had to type the same data into the database multiple times. This kind of inconsistency causes all kinds of problems. If you require a data entry person to enter the same data repeatedly, you will see inconsistencies in the results. Different people will choose different abbreviations. You may see multiple spellings of the same term. Some people will simply not enter data if it's too difficult. When this happens, you cannot rely on the data (is it a zoo or a circus?). You also can't search the data with confidence. (If I look for all operatives in Lower Volta, I'll miss Blackford, because he's listed as being in Lower Votla.) If you look carefully, you'll notice that I misspelled "sabatoge." It will be very difficult to find all places this word is misspelled and fix them all.
Problem with the Operation Information
There's another problem with this database. If for some reason Agent Rahab were dropped from the database (maybe she was a double agent all along), the information regarding Operation Raging Dandelion would be deleted along with her record, because the only place it is stored is as a part of her record. The operation's data somehow needs to be stored separately from the agent data.
Problems with Listed Fields
The specialty field brings its own troubles to the database. This field can contain more than one entity, because spies should be able to do more than one thing. (My favorite combination is explosives and flower arranging.) Fields with lists in them can be problematic. For one thing, it's much harder to figure out what size to make a field that may contain several entities. If your most talented spy has ten different skills, you would need enough room to store all ten skills in every spy's record. It can be difficult to search on fields that contain lists of data. You might be tempted to put several different skill fields (maybe a skill1, skill2, and skill3 field, for example) but this doesn't completely solve the problem. It would be better to have a more flexible system that can accommodate any number of skills. The flat file system you've seen in this badSpy database is not capable of that kind of versatility.
