Building Your Data Tables

Setting Up the System

I began my SQL script with some comments that describe the database and a few design decisions I made when building the database.

######################################
# buildSpy.sql
# builds and populates all databases for spy examples
# uses mysql -should adapt easily to other rdbms
# by Andy Harris for PHP/MySQL for Abs. Beg
######################################

######################################
# conventions
######################################
# primary key = table name . ID
# primary key always first field
# all primary keys autonumbered
# all field names camel-cased
# only link tables use underscore
# foreign keys indicated although mySQL does not enforce
# every table used as foreign reference has a name field
######################################

######################################
#housekeeping
######################################

use chapter9;
DROP TABLE IF EXISTS badSpy;
DROP TABLE IF EXISTS agent;
DROP TABLE IF EXISTS operation;
DROP TABLE IF EXISTS specialty;
DROP TABLE IF EXISTS agent_specialty;
DROP TABLE IF EXISTS spyFirst;

Notice that I specified a series of conventions. These self-imposed rules will help make my database easier to manage when things get complicated. Some of the rules might not make sense yet (because I haven't identified what a foreign key is, for instance), but the important thing is I have clearly identified some rules that will help me later on.

The code then specifies the chapter9 database, and deletes all tables if they already existed. This behavior ensures I'll start with a fresh version of the data.

Creating the agent Table

The normalized agent table is actually quite simple. The actual table is shown in Table 9.3.

The only data remaining in the agent table is the agent's name and a numerical field for the operation. The operationID field is used as the glue that holds together the agent and operation tables.

I've added a few things to improve the SQL code for creating the agent table to ensure that it behaves well.

CREATE TABLE agent (
  agentID int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50) default NULL,
  operationID int(11) default NULL,
  PRIMARY KEY (agentID),
  FOREIGN KEY (operationID) REFERENCES operation (operationID)
);

Recall that the first field in a table is usually called the primary key. Primary keys must be unique and each record must have one. I've also chosen to name each primary key according to a special convention. Primary key names will always begin with the table name and end with "ID." I added this convention because it will make things easier later on when I write programs to work with this data. The NOT NULL modifier ensures that all records of this table must have a primary key. The AUTO_INCREMENT identifier is a special tool that allows MySQL to pick a new value for this field if no value is specified. This will ensure that all entries are unique.

I've added an indicator at the end of the CREATE TABLE statement to indicate that agentID is the primary key of the agent table.

INSERT INTO agent VALUES(
  null, 'Bond', 1
);

Building the operation Table

The new operation table contains information referring to an operation. (See Table 9.4 for descriptions of some operation IDs.)

Each operation gets its own record in the operation table. All the data corresponding to an operation is stored in the operation record. Each operation's data is stored only one time. This has a number of positive effects:

• It's only necessary to enter operation data once per op, saving time on data entry.

• Since there's no repeated data, you won't have data inconsistency problems (like the circus/zoo problem).

• The new database will take less space, because there's no repeated data.

• The operation is not necessarily tied to an agent, so you won't accidentally delete all references to an operation by deleting the only agent assigned to that mission (remember, this could happen with the original data design).

• If you need to update operation data, you don't need to go through every agent to figure out which ones were assigned to that operation (again, you would have had to do this with the old database design).

The SQL used to create the operation table is much like that used for the agent table:

CREATE TABLE operation (
  operationID int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50) default NULL,
  description varchar(50) default NULL,
  location varchar(50) default NULL,
  PRIMARY KEY ('OperationID')
);


INSERT INTO operation VALUES(
  null, 'Dancing Elephant',
  'Infiltrate suspicious zoo', 'London'
);

As you can see, the operation table conforms to the rules of normalization, and it also is much like the agent table. Notice that I'm being very careful about how I name things. SQL is (theoretically) case-insensitive, but I've found that this is not always true (especially in MySQL, where the Windows versions appear not concerned about case, but UNIX versions will treat operationID and OperationID as different field names). I specified that all field names will use 'camel-case' (just like you've been doing with your PHP variables). I have also named the key field according to my own formula (table name followed by "ID").

Using a Join to Connect Tables

The only downside to disconnecting the data tables is the necessity to somehow rejoin the data when needed. The user really doesn't care that the operation and the agent are in different tables, but he or she still will want the data to be visible as if they were on the same table. The secret to re-attaching tables is a tool called the inner join. Take a look at the following SELECT statement in SQL:

SELECT agent.name AS agent, operation.name AS operation
FROM agent, operation
WHERE agent.operationID = operation.operationID
ORDER BY agent.name;

At first glance this looks like an ordinary query, but it is a little different because it joins up data from two different tables. Table 9.5 illustrates the results of this query.

Creating Useful Joins

An SQL query can pull data from more than one table. To do this, there a couple of basic rules.

First, you might need to specify the field names more formally. Notice that the SELECT statement specifies agent.name rather than simply name. This is necessary because both tables contain a field called name. Using the table.field syntax is much like using a person's first and last name. It's not necessary if there's no chance of confusion, but in a larger environment the more complete naming scheme can avoid confusion.

Also, note the use of the AS clause. This provides an alias for the column and provides a nicer output.

The FROM clause up to now has only specified one table. In this example, it's necessary to specify that data will be coming from two different tables.

Examining a Join Without a WHERE Clause

The WHERE clause helps to clarify the relationship between the two tables. As an explanation, consider the following query:

SELECT
  agent.name AS 'agent',
  agent.operationID as 'agent opID',
  operation.operationID as 'op opID',
  operation.name AS 'operation'
FROM agent, operation
ORDER BY agent.name;

This query is much like the earlier query, except it includes the operationID field from each table and it omits the WHERE clause. You might be surprised by the results.

Adding a WHERE Clause to Make a Proper Join

Without a WHERE clause, all possible combinations are returned. The only records we're concerned with are those where the operationID fields in the agent table and in the operation table have the same value. The WHERE clause returns only these values joined by a common operation ID.

The secret to making this work is the operationID fields in the two tables. You've already learned that each table should have a primary key. The primary key field is used to uniquely identify each record in a database. In the agents table, agentID is the primary key. In operations, operationID is the primary key. (You might note my unimaginative but very useful naming convention here) I was able to take all data that refers to the operation out of the agent table by replacing those fields with a field that points to the primary key of the operations table. A field that references the primary key of another table is called a foreign key. Primary and foreign keys cement the relationships between tables. See Table 9.6.

Adding a Condition to a Joined Query

Of course, you can still use the WHERE clause to limit which records are shown. Use the AND structure to build compound conditions. For example, this code:

SELECT
  agent.name AS 'agent',
  operation.name AS operation
FROM agent, operation
WHERE agent.operationID = operation.operationID
  AND agent.name LIKE 'B%';

will return the code name and operation name of every agent whose code name begins with "B."

THE TRUTH ABOUT INNER JOINS

You should know that the syntax I provided here is a convenient shortcut supported by most DBMS systems. The formal syntax of the inner join looks like this:

SELECT agent.name, operation.name
FROM
  agent INNER JOIN operation
  ON agent.OperationID = operation.OperationID
ORDER BY agent.name;

Many data programmers prefer to think of the join as part of the WHERE clause and use the WHERE syntax. A few SQL databases (notably many offerings from Microsoft) do not allow the WHERE syntax for inner joins, and require the INNER JOIN to be specified as part of the FROM clause. When you use this INNER JOIN syntax, the ON clause indicates how the tables will be joined.