Programming instructions

Designing the database for your application 53
Designing the database for your application
After you identify the information to collect, you must consider where to store the data.
Prior to creating the data collection form and instructing ColdFusion where to store the
form data, you must have a database ready to accept the data.
If you had to create the Compass Travel database, you would create a table named Trips
to store the information that you plan to collect about each trip. The table would look
something like this:
Recognizing the data types
Each field in the Trips table has a data type attribute that describes the type of data that
can be stored in the column. For instance, the tripName column can contain text data
while the price column can only contain numeric data. It is important to know what type
of data is valid for each column so that your data collection forms can validate against
incorrect values entered by the user.
Looking closer, you might wonder why the eventType column is a number and not a text
data type column. Recall that data requirements analysis identified the need for a type of
event (surfing, mountain climbing, kayaking, and so on). The purpose of this column is
to classify trips into various categories based on the trip activity. It is essential that the
application classifies the trips consistently. Therefore, it is important to offer a list of
event types for the user to select, rather than to accept free text input.
To present a list of event types for user selection, the event types are stored in a separate
table, the Eventtypes table. This table is already populated and contains the following
rows: