Database Reference
In-Depth Information
The scripts in this chapter are intended to be invoked from your browser after they have
been installed, but you can invoke many of them (JSP pages excepted) from the com‐
mand line to see the raw HTML they produce; see
Recipe 18.2
.
To provide a concrete context for discussion, many of the form-processing examples in
this chapter are based on the following scenario. You run a business in the lucrative
“construct-a-cow” market that manufactures built-to-order ceramic bovine figurines,
and you want to design an online ordering application that lets customers make selec‐
tions for several aspects of the product. For each order, it's necessary to collect several
types of information:
Cow color
The particular list of colors available at any particular time changes occasionally,
so for flexibility, the values can be stored in a database table. To change the set of
colors from which customers can choose, just update the table.
Cow size
There is a fixed set of sizes that doesn't change often (small, medium, large), so the
values can be represented as elements of an
ENUM
column.
The all-important cow accessory items
These include a bell, horns, a sporty-looking tail ribbon, and a nose ring. Accessories
can be represented in a
SET
column because a customer may want to select more
than one of them. In addition, you know from past experience that most customers
order horns and a cow bell, so it's reasonable to use those for the column's default
value.
Customer name and address (street, city, state)
The possible state names are already stored in the
states
table. We can use them
as the basis for the corresponding form element.
Given the preceding discussion, a
cow_order
table can be designed like this:
CREATE
TABLE
cow_order
(
id
INT
UNSIGNED
NOT
NULL
AUTO_INCREMENT
,
#
cow
color
,
figurine
size
,
and
accessory
items
color
VARCHAR
(
20
),
size
ENUM
(
'small'
,
'medium'
,
'large'
)
DEFAULT
'medium'
,
accessories
SET
(
'cow bell'
,
'horns'
,
'nose ring'
,
'tail ribbon'
)
DEFAULT
'cow bell,horns'
,
#
customer
name
,
street
,
city
,
and
state
(
abbreviation
)
cust_name
VARCHAR
(
40
),
cust_street
VARCHAR
(
40
),
cust_city
VARCHAR
(
40
),
cust_state
CHAR
(
2
),
PRIMARY
KEY
(
id
)
);
