Specifications

the auto_increment facility so that MySQL can manage these for us—it’s one less thing to
worry about.
The other columns are all going to hold string type data. We’ve chosen the char type for these.
This specifies fixed width fields. The width is specified in the brackets, so, for example, name
can have up to 30 characters.
This data type will always allocate 30 characters of storage for the name, even if they’re not
all used. MySQL will pad the data with spaces to make it the right size. The alternative is
varchar, which uses only the amount of storage required (plus one byte). It’s a small trade
off—varchars will use less space but chars are faster.
For real customers with real names and real addresses, these column widths will be far too
narrow.
Note that we’ve declared all the columns as NOT NULL. This is a minor optimization you can
make wherever possible that also will make things run a bit faster.
We’ll talk more about optimization in Chapter 11.
Some of the other CREATE statements have variations in syntax. Let’s look at the orders table:
create table orders
( orderid int unsigned not null auto_increment primary key,
customerid int unsigned not null,
amount float(6,2),
date date not null
);
The amount column is specified as a floating point number of type float. With most floating
point data types, you can specify the display width and the number of decimal places. In this
case, the order amount is going to be in dollars, so we’ve allowed a reasonably large order total
(width 6) and two decimal places for the cents.
The date column has the data type date.
In this particular table, we’ve specified that all columns bar the amount as NOT NULL. Why?
When an order is entered into the database, we’ll need to create it in orders, add the items to
order_items, and then work out the amount. We might not know the amount when the order is
created, so we’ve allowed for it to be NULL.
The books table has some similar characteristics:
create table books
( isbn char(13) not null primary key,
author char(30),
title char(60),
price float(4,2)
);
Creating Your Web Database
C
HAPTER 8
8
CREATING YOUR
WEB DATABASE
197
11 7842 CH08 3/6/01 3:38 PM Page 197