Specifications

Julie Smith from the Customers table for example. If I open my telephone directory, there are
too many listings of that name to count.
We could distinguish Julie in several ways. Chances are, she’s the only Julie Smith living at
her address. Talking about “Julie Smith, of 25 Oak Street, Airport West” is pretty cumbersome
and sounds too much like legalese. It also requires using more than one column in the table.
What we have done in this example, and what you will likely do in your applications, is assign
a unique CustomerID. This is the same principle that leads to you having a unique bank
account number or club membership number. It makes storing your details in a database easier.
An artificially assigned identification number can be guaranteed to be unique. Few pieces of
real information, even if used in combination, have this property.
The identifying column in a table is called the key or the primary key. A key can also consist of
multiple columns. If for example, we had chosen to refer to Julie as “Julie Smith, of 25 Oak
Street, Airport West,” the key would consist of the Name, Address, and City columns and could
not be guaranteed to be unique.
Databases usually consist of multiple tables and use a key as a reference from one table to
another. In Figure 7.2, we’ve added a second table to the database. This one stores orders
placed by customers. Each row in the Orders table represents a single order, placed by a single
customer. We know who the customer is because we store their CustomerID. We can look at
the order with OrderID 2, for example, and see that the customer with CustomerID 1 placed it.
If you then look at the Customers table, you can see that CustomerID 1 refers to Julie Smith.
Using MySQL
P
ART II
174
CustomerID
CUSTOMERS
Name Address City
1 Julie Smith 25 Oak Street Airport West
2 Alan Wong 1/47 Haines Avenue Box Hill
3 Michelle Arthur 357 North Road Yarraville
OrderID
ORDERS
CustomerID Amount Date
1 3 27.50 02-Apr-2000
2 1 12.99 15-Apr-2000
3 2 74.00 19-Apr-2000
4 4 6.99 01-May-2000
FIGURE 7.2
Each order in the Orders table refers to a customer from the Customers table.
10 7842 CH07 3/6/01 3:34 PM Page 174