Building an Online Catalog

nline catalogs are everywhere on the Web. Every business that has products for sale can use an online catalog. Some businesses use online
catalogs to sell their products online, and some use them to show the quality and worth of their products to the world. Many customers have come to expect businesses to be online and provide information about their products. Customers often begin their search for a product online, researching its avail­ ability and cost through the Web.

In this chapter, you find out how to build an online catalog. I chose a pet store catalog for no particular reason except that it sounded like more fun than a catalog of socks or light bulbs. And looking at the pictures for a pet catalog was much more fun than looking at pictures of socks. I introduce the Pet Catalog example in Chapter 3 and use it for many of the examples throughout this book.

In general, all catalogs do the same thing: provide product information to potential customers. The general purpose of the catalog is to make it as easy as possible for customers to see information about the products. In addition, you want to make the products look as attractive as possible so that cus­ tomers will want to purchase them.

Designing the Application

The first step in design is to decide what the application should do. The obvi­ ous purpose of the Pet Catalog is to show potential customers information about the pets. A pet store might also want to show information about pet

products, such as pet food, cages, fish tanks, and catnip toys . . . but you decide not to include such items in your catalog. The purpose of your online catalog application is just to show pets.

For the customer, displaying the information is the sole function of the cata­ log. However, from your perspective, the catalog also needs to be maintained; that is, you need to add items to the catalog. So, you must include the task of adding items to the catalog as part of the catalog application. Thus, the appli­ cation has two distinct functions:

Show pets to the customers

Add pets to the catalog

Showing pets to the customers

The basic purpose of your online catalog is to let customers look at pets. Customers can’t purchase pets online, of course. Sending pets through the mail isn’t feasible. But a catalog can showcase pets in a way that motivates customers to rush to the store to buy them.

If your catalog only has three pets in it, your catalog can be pretty simple — one page showing the three pets. However, most catalogs have many more items than that. Usually, a catalog opens with a list of the types of products — in this case, pets — that are available, such as cat, dog, horse, and dragon. Customers select the type of pet they want to see, and the catalog then dis­ plays the individual pets of that type. For example, if the customer selects dog, the catalog would then show collies, spaniels, and wolves. Some types of prod­ ucts might have more levels of categories before you see individual products. For instance, furniture might have three levels rather than two. The top level might be the room, such as kitchen, bedroom, and so on. The second level might be type, such as chairs, tables, and so on. The third level would be the individual products.

The purpose of a catalog is to motivate those who look at it to make a purchase immediately. For the Pet Catalog, pictures are a major factor in motivating cus­ tomers to make a purchase. Pictures of pets make people go ooooh and aaaah and say, “Isn’t he cuuuute!” This generates sales. The main purpose of your Pet Catalog is to show pictures of pets. In addition, the catalog also should show descriptions and prices.

To show the pets to customers, the Pet Catalog will do the following:

1. Show a list of the types of pets and allow the customer to select a type.

2. Show information about the pets that match the selected type. The information includes the description, the price, and a picture of the pet.

Adding pets to the catalog

You can add items to your catalog several ways. However, the task of adding an item to the catalog is much easier if you use an application designed for adding your specific products. In many cases, you aren’t the person who will be adding products to your catalog. One reason for adding maintenance func­ tionality to your catalog application is so that someone else can do those boring maintenance tasks. The easier it is to maintain your catalog, the less likely that errors will sneak into it.

An application to add a pet to your catalog should do the following:

1. Prompt the user to enter a pet type for the pet. A selection list of possi­ ble pet types would eliminate many errors, such as alternate spellings (dog and dogs) and misspellings. The application also needs to allow the user to add new categories when needed.

2. Prompt the user to enter a name for the pet, such as collie or shark.
A selection list of names would help prevent mistakes. The application also needs to allow the user to add new names when needed.

3. Prompt the user to enter the pet information for the new pet. The application should clearly specify what information is needed.

4. Store the information in the catalog.

The catalog entry application can check the data for mistakes and enter the data into the correct locations. The person entering the new pet doesn’t need to know the inner workings of the catalog.

Building the Database

The catalog itself is a database. It doesn’t have to be a database; it’s possible to store a catalog as a series of HTML (HyperText Markup Language) files that contain the product information in HTML tags and display the appropriate file when the customer clicks a link. However, it makes my eyes cross to think of maintaining such a catalog. Imagine the tedium of adding and removing cata­ log items manually . . . or finding the right location for each item by searching through many files. Ugh. For these reasons, putting your Pet Catalog in a data­ base is better.

The PetCatalog database contains all the information about pets. It uses three tables:

Pet table

PetType table

Color table

The first step in building the Pet Catalog is to build the database. It’s pretty much impossible to write programs without a working database to test the programs on. First you design your database; then you build it; then you add the data (or at least some sample data to use while developing the programs).

Some changes have been made to the database design in Chapter 3 for the Pet Catalog. Development and testing often result in changes. Perhaps you find that you didn’t take some factors into consideration in your design or that certain elements of your design don’t work with real-world data or are difficult to program. It’s perfectly normal for the design to evolve while you work on your application. Just be sure to change your documentation when your design changes.

Building the Pet table

In your design for the Pet Catalog, the main table is the Pet table. It contains the information about the individual pets that you sell. The following SQL query creates the Pet table:

CREATE TABLE Pet (
petID INT(5) NOT NULL AUTO_INCREMENT, petName CHAR(25) NOT NULL,
petType CHAR(15) NOT NULL DEFAULT “Misc”, petDescription VARCHAR(255),
price DECIMAL(9,2),
pix CHAR(15) NOT NULL DEFAULT “na.gif”, PRIMARY KEY(petID) );

Each row of the Pet table represents a pet. The columns are as follows:

petID: A sequence number for the pet. In another catalog, this might be a product number, a serial number, or a number used to order the prod­ uct. The CREATE query defines the petID column in the following ways:

• INT(5): The data in the field is expected to be a numeric integer. The database won’t accept a character string in this field.

• PRIMARY KEY(petID): This is the primary key, which is the field that must be unique. MySQL will not allow two rows to be entered with the same petID.

• NOT NULL: This definition means that this field can’t be empty. It must have a value. The primary key must always be set to NOT NULL.

• AUTO-INCREMENT: This definition means that the field will automat­ ically be filled with a sequential number if you don’t provide a spe­ cific number. For example, if a row is added with 98 for a petID,
the next row will be added with 99 for the petID unless you spec­
ify a different number. This is a useful way of specifying a column

with a unique number, such as a product number or an order number. You can always override the automatic sequence number with a number of your own, but if you don’t provide a number, a sequential number is stored.

petName: The name of the pet, such as lion, collie, or unicorn. The
CREATE query defines the petName column in the following ways:

• CHAR(25): The data in this field is expected to be a character string that’s 25 characters long. The field will always take up 25 characters of storage, with padding if the actual string stored is less than 25 characters.

• NOT NULL: This definition means that this field can’t be empty. It must have a value. After all, it wouldn’t make much sense to have a pet in the catalog without a name.

• No default value: If you try to add a new row to the Pet table with­ out a petName, it won’t be added. It doesn’t make sense to have a default name for a pet.

petType: The type of pet, such as dog or fish. The CREATE query defines the petType column in the following ways:

• CHAR(15): The data in this field is expected to be a character string that’s 15 characters long. The field will always take up 15 characters of storage, with padding if the actual string stored is less than 15 characters.

• NOT NULL: This definition means that this field can’t be empty. It must have a value. The online catalog application will show cate­ gories first and then pets within a category, so a pet with no cate­ gory will never be shown on the Web page.

• DEFAULT “Misc”: The value “Misc” is stored if you don’t provide a value for petType. This ensures that a value is always stored for petType.

petDescription: A description of the pet. The CREATE query defines the petDescription in the following way:

• VARCHAR(255): This data type defines the field as a variable char­ acter string that can be up to 255 characters long. The field is stored in its actual length.

price: The price of the pet. The CREATE query defines price in the fol­
lowing way:

• DECIMAL(9,2): This data type defines the field as a decimal number that can be up to nine digits and has two decimal places. If you store an integer in this field, it will be returned with two deci­ mal places, such as 9.00 or 2568.00.

pix: The filename of the picture of the pet. Pictures on a Web site are stored in graphic files with names like dog.jpg, dragon.gif, or cat.png. This field stores the filename for the picture that you want to show for this pet. The CREATE query defines pix in the following ways:

• CHAR(15): The data in this field is expected to be a character string that’s 15 characters long. For some applications, the picture files might be in other directories or on other Web sites requiring a longer field, but for this application, the pictures are all in a direc­ tory on the Web site and have short names. The field will always take up 15 characters of storage, with padding if the actual string stored is less than 15 characters.

• NOT NULL: This definition means that this field can’t be empty. It must have a value. You need a picture for the pet. When a Web site tries to show a picture that can’t be found, it displays an ugly error message in the browser window where the graphic would go. You don’t want your catalog to do that, so your database should
require a value. In this case, you define a default value so that a value will always be placed in this field.

• DEFAULT “na.gif”: The value “na.gif” is stored if you don’t pro­ vide a value for pix. In this way, a value is always stored for pix. The na.gif file might be a graphic that reads something like: “picture not available”.

Notice the following points about this database table design:

Some fields are CHAR, and some are VARCHAR. CHAR fields are faster, whereas VARCHAR fields are more efficient. Your decision will depend on whether disk space or speed is more important for your application in your environment.

In general, shorter fields should be CHAR because shorter fields don’t waste much space. For instance, if your CHAR is 5 characters, the most space that you could possibly waste is 4. However, if your CHAR is 200, you could waste 199. Therefore, for short fields, use CHAR for speed with very little wasted space.

The petID field means different things for different pets. The petID field assigns a unique number to each pet. However, a unique number is not necessarily meaningful in all cases. For example, a unique number
is meaningful for an individual kitten but not for an individual goldfish.

There are really two kinds of pets. One is the unique pet, such as a puppy or a kitten. After all, the customer buys a specific dog — not just a generic dog. The customer needs to see the picture of the actual animal. On the other hand, some pets are not especially unique, such as
a goldfish or a parakeet. When customers purchase a goldfish, they see a

tank full of goldfish and point at one. The only real distinguishing char­ acteristic of a goldfish is its color. The customer doesn’t need to see a picture of the actual fish in your catalog, just a picture of a generic gold­ fish, perhaps showing the possible colors.

In your catalog, you have both kinds of pets. The catalog might contain several pets with the name cat but with different petIDs. The picture would show the individual pet. The catalog also contains pets that aren’t individuals but that represent generic pets, such as goldfish. In this
case, there’s only one entry with the name goldfish, with a single petID.

I’ve used both kinds of pets in this catalog to demonstrate the different kinds of products that you might want to include in a catalog. The unique item catalog might include such products as artwork or vanity license plates. When the unique item is sold, it’s removed from the catalog. Most products are more generic, such as clothing or automobiles. Although a picture shows a particular shirt, many identical shirts are available. You can sell the shirt many times without having to remove it from the catalog.

Building the PetType table

You assign each pet a type, such as dog or dragon. The first Web page of the catalog lists the types for the customer to select from. A description of each type is also helpful to show. You don’t want to put the type description in the main Pet table because the description would be the same for all pets with the same category. Repeating information in a table violates good database design.

The PetCatalog database includes a table called PetType that holds the type descriptions. The following SQL query creates the PetType table:

CREATE TABLE PetType (
petType CHAR(15) NOT NULL, typeDescription VARCHAR(255),
PRIMARY KEY(petType) );

Each row of this table represents a pet type. These are the columns:

petType: The type name. Notice that the petType column is defined the same in the Pet table (which I describe in the preceding section) and in this table. This makes table joining possible and makes matching rows in the tables much easier. However, the petType is the primary key in this table but not in the Pet table. The CREATE query defines the petType column in the following ways:

• CHAR(15): The data in this field is expected to be a character string that’s 15 characters long.

• PRIMARY KEY(petType): This definition sets the petType column as the primary key. This is the field that must be unique. MySQL will not allow two rows to be entered with the same petType.

• NOT NULL: This definition means that this field can’t be empty. It must have a value. The primary key must always be set to NOT NULL.

typeDescription: A description of the pet type. The CREATE query
defines the typeDescription in the following way:

• VARCHAR(255): The string in this field is expected to be a variable character string that can be up to 255 characters long. The field is stored in its actual length.

Building the Color table

When I discuss building the Pet table (see “Building the Pet table,” earlier in this chapter), I discuss the different kinds of pets: pets that are unique (such as puppies and horses) and pets that are not unique (such as goldfish and tur­ tles). For unique pets, the customer needs to see a picture of the actual pet. For pets that aren’t unique, the customer only needs to see a generic picture.

In some cases, generic pets come in a variety of colors, such as blue parakeets and green parakeets. You might want to show two pictures for parakeets: a pic­ ture of a blue parakeet and a picture of a green parakeet. However, because most pets aren’t this kind of generic pet, you don’t want to add a color column to your main Pet table because it would be blank for most of the rows. Instead, you create a separate table containing only pets that come in more than one color. Then when the catalog application is showing pets, it can check the Color table to see whether there’s more than one color available — and if
there is, it can show the pictures from the Color table.

The Color table points to pictures of pets when the pets come in different colors so that the catalog can show pictures of all the available colors. The following SQL query creates the Color table:

CREATE TABLE Color (
petName CHAR(25) NOT NULL, petColor CHAR(15) NOT NULL,
pix CHAR(15) NOT NULL DEFAULT “na.gif”, PRIMARY KEY(petName,petColor) );

Each row represents a pet type. The columns are as follows:

petName: The name of the pet, such as lion, collie, or Chinese bearded dragon. Notice that the petName column is defined the same in the Pet table and in this table. This makes table joining possible and makes matching rows in the tables much easier. However, the petName is the primary key in this table but not in the Pet table. The CREATE query defines the petName in the following ways:

• CHAR(25): The data in this field is expected to be a character string that is 25 characters long.

• PRIMARY KEY(petName,petColor): The primary key must be unique. For this table, two columns together are the primary key — this column and the petColor column. MySQL won’t allow two rows to be entered with the same petName and petColor.

• NOT NULL: This definition means that this field can’t be empty. It must have a value. The primary key must always be defined as NOT NULL.

petColor: The color of the pet, such as orange or purple. The CREATE
query defines the petColor in the following ways:

• CHAR(15): This data type defines the field as a character string that’s 15 characters long.

• PRIMARY KEY(petName,petColor): The primary key must be unique. For this table, two columns together are the primary key — this column and the petName column. MySQL won’t allow two rows to be entered with the same petName and petColor.

• NOT NULL: This definition means that this field can’t be empty. It must have a value. The primary key must always be defined as NOT NULL.

pix: The filename containing the picture of the pet. The CREATE query defines pix in the following ways:

• CHAR(15): This data type defines the field as a character string that is 15 characters long.

• NOT NULL: This definition means that this field can’t be empty. It must have a value. You need a picture for the pet. When a Web site tries to show a picture that can’t be found, it displays an ugly error message in the browser window where the graphic would go. You don’t want your catalog to do that, so your database should
require a value. In this case, the CREATE query defines a default value so that a value will always be placed in this field.

• DEFAULT “na.gif”: The value “na.gif” is stored if you don’t pro­ vide a value for pix. In this way, a value is always stored for pix. The file na.gif might contain a graphic that reads something like picture not available.

Adding data to the database

You can add the data to the database many ways. You can use SQL queries
to add pets to the database, or you can use the application that I describe in this chapter. My personal favorite during development is to add a few sample items to the catalog by reading the data from a file. Then, whenever my data

becomes totally bizarre during development (as a result of programming errors or my weird sense of humor), I can re-create the data table in a moment. Just DROP the table, re-create it with the SQL query, and reread the sample data.

For example, the data file for the Pet table might look like this:

<TAB>Pekinese<TAB>Dog<TAB>Small, cute, energetic. Good alarm system.<TAB>100.00<TAB>peke.jpg
<TAB>House cat<TAB>Cat<TAB>Yellow and white cat. Extremely playful. <TAB>20.00<TAB>catyellow.jpg
<TAB>House cat<TAB>Cat<TAB>Black cat. Sleek, shiny. Likes children. <TAB>20.00<TAB>catblack.jpg
<TAB>Chinese Bearded Dragon<TAB>Lizard<TAB>Grows up to 2 feet long. Fascinating to watch. Likes to be held.<TAB>100.00<TAB>lizard.jpg
<TAB>Labrador Retriever<TAB>Dog<TAB>Black dog. Large, intelligent retriever. Often selected as guide dogs for the blind.<TAB>100.00<TAB>lab.jpg
<TAB>Goldfish<TAB>Fish<TAB>Variety of colors. Inexpensive.
Easy care. Good first pet for small children.<TAB>2.00<TAB>goldfish.jpg
<TAB>Shark<TAB>Fish<TAB>Sleek. Powerful. Handle with care.<TAB>200.00<TAB>shark.jpg
<TAB>Asian Dragon<TAB>Dragon<TAB>Long and serpentine. Often gold or red.<TAB>10000.00<TAB>dragona.jpg
<TAB>Unicorn<TAB>Horse<TAB>Beautiful white steed with spiral horn on forehead.<TAB>20000.00<TAB>unicorn.jpg

These are the data file rules:

The <TAB> tags represent real tabs — the kind that you create by press­
ing the Tab key.

Each line represents one pet and must be entered without pressing the Enter or Return key. The lines in the preceding example are shown wrapped to more than one line so that you can see the whole line. However, in the actual file, the data lines are one on each line.

A tab appears at the beginning of each line because the first field is not being entered. The first field is the petID, which is entered automati­ cally; you don’t need to enter it. However, you do need to use a tab so that MySQL knows there’s a blank field at the beginning.

You can then use an SQL query to read the data file into the Pet table:

LOAD DATA LOCAL INFILE “pets” INTO TABLE Pet;

Any time that the data table gets odd, you can re-create it and read the data in again.

The LOAD DATA LOCAL query might not be available in your version of MySQL. This query must be enabled before you can use it. If it’s not enabled, you will see an error that reads The used command is not allowed with this MySQL version. I discuss LOAD DATA LOCAL in detail in Chapter 4.

Designing the Look and Feel

After you know what the application is going to do and what information the database contains, you can design the look and feel of the application. The look and feel includes what the user sees and how the user interacts with the appli­ cation. Your design should be attractive and easy to use. You can plan out this design on paper, indicating what the user sees, perhaps with sketches or with written descriptions. In your design, include the user interaction components, such as buttons or links, and describe their actions. You should include each page of the application in the design. If you’re lucky, you have a graphic designer who can develop beautiful Web pages for you. If you’re me, you just
do your best with a limited amount of graphic know-how.

The Pet Catalog has two look and feel designs: one for the catalog that the customer sees; and another, less fancy one for the part of the application that you or whoever is adding pets to the catalog uses.

Showing pets to the customers

The application includes three pages that customers see:

The storefront page: This is the first page that customers see. It states the name of the business and the purpose of the Web site.

The pet type page: This page lists all the types of pets and allows cus­
tomers to select which type of pet they want to see.

The pets page: This page shows all the pets of the selected type.

Storefront page
The storefront page is the introductory page for the Pet Store. Because most people already know what a pet store is, this page doesn’t need to provide much explanation. Figure 11-1 shows the storefront page. The only customer action available on this page is a link that the customer can click to see the Pet Catalog.

Pet type page
The pet type page lists all the types of pets in the catalog. Each pet type is listed with its description. Figure 11-2 shows the pet type page. Radio buttons appear next to each pet type so that customers can select the type of pet that they want to see.
Adding pets to the catalog

The application includes three pages that customers don’t see; these are the pages used to add pets to the Pet Catalog. The three pages work in sequential order to add a single pet:

1. Get pet type page. The person adding a pet to the catalog selects the radio button for the pet type. The user can also enter a new pet type.

2. Get pet information page. The user selects the radio button for the pet being added and fills in the pet description, price, and picture filename. The user can also enter a new pet name.

3. Feedback page. A page is displayed showing the pet information that was added to the catalog.

Get pet type page
The first page gets the pet type for the pet that needs to be added to the cata­ log. Figure 11-6 shows the get pet type page. Notice that all the pet types cur­ rently in the catalog are listed, and a section is provided where the user can enter a new pet type if it’s needed.

Get pet information page
Figure 11-7 shows the second page. This page lets the user type the informa­ tion about the pet that goes in the catalog. This page lists all the pet names in the catalog for the selected pet type so that the user can select one. It also provides a section where the user can type a new pet name if needed.

Feedback page
When the user submits the pet information, the information is added to the PetCatalog database. Figure 11-8 shows a page that verifies the information that was added to the database. The user can click a link to return to the first page and add another pet.

Get missing information page
The application checks the data to see that the user entered the required information and prompts the user for any information that isn’t entered. For instance, if the user selects New Category on the first page, the user must type a category name and description. If the user doesn’t type the name or the description, a page is displayed that points out the problem and requests the information. Figure 11-9 shows the page that users see if they forget to type the category name and description.