In this chapter you will build a database to manage your international spy ring. (You do have an international spy ring, don't you?) Saving the world is a complicated task, so you'll need a database to keep track of all your agents. Secret agents are assigned to various operations around the globe, and certain agents have certain skills. The examples in this chapter will take you through the construction of such a database. You'll see how to construct the database in MySQL. In the final chapter, you'll use this database to make a really powerful spymaster application in PHP.
The spy database reflects a few facts about my spy organization (called the Pantheon of Humanitarian Performance, or PHP):
Each agent has a code name.
Each agent can have any number of skills.
More than one agent can have the same skill.
Each agent is assigned to one operation at a time.
More than one agent can be assigned to one operation.
The location of a spy is determined by the operation.
Each operation has only one location.
This list of rules helps to explain some characteristics of the data. In database parlance, they are called business rules. I'll need to design the database so that these rules are enforced.
I set up this particular set of rules in a somewhat arbitrary way because they help make my database as simple as possible while still illustrating most of the main problems you'll encounter in data design. Usually you don't get to make up business rules. Instead, you'll need to learn them by talking to those who use the data every day.