How to add tables to a database?

You can add tables to any database, whether it’s a new, empty database that you just created or an existing database that already has tables and data in it. You use the CREATE query to add tables to a database. Because a table is created in a database, you must indicate the database name where you want the table created. That is, when using the form , you must type a database name in the top field. If you don’t, you see the error message No Database Selected.
The query to add a table begins with

CREATE TABLE tablename

Next comes a list of column names with definitions. The information for each column is separated from the information for the next column by a comma. The entire list is enclosed in parentheses. Each column name is followed by its data type and any other definitions required. Here are some definitions that you can use:

• NOT NULL: This column must have a value; it can’t be empty.
  
• DEFAULT value: This value is stored in the column when the row is created if no other value is given for this column.
• AUTO_INCREMENT: You use this definition to create a sequence number. As each row is added, the value of this column increases by one integer from the last row entered. You can override the auto number by assigning a specific value to the column.
• UNSIGNED: You use this definition to indicate that the values for this numeric field will never be negative numbers.

The last item in a CREATE TABLE query indicates which column or combination of columns is the unique identifier for the row — the primary key.
Each row of a table must have a field or a combination of fields that is different for each row. No two rows can have the same primary key. If you attempt to add a row with the same primary key as a row already in the table, you get an error message, and the row is not added. The database design identifies the primary key. You specify the primary key by using the following format:

CREATE TABLE Member (
loginName VARCHAR(20) NOT NULL,
createDate DATE NOT NULL),
PRIMARY KEY(columnname) )

The columnname is enclosed in parentheses. If you’re using a combination of columns as the primary key, include all the column names, separated by commas. For instance, you would designate the primary key for the Login table in the MemberDirectory database by using this in the CREATE query:
PRIMARY KEY (loginName,loginTime)
Listing below shows the CREATE TABLE query used to create the Member table of the MemberDirectory database. You could enter this query on a single line if you wanted to. MySQL doesn’t care how many lines you use. However, the format shown in Listing below makes it easier to read. This human-friendly format also helps you spot typos.

CREATE TABLE Member (
loginName VARCHAR(20) NOT NULL,
createDate DATE NOT NULL,
password CHAR(255) NOT NULL,
lastName VARCHAR(50),
firstName VARCHAR(40),
street VARCHAR(50),
city VARCHAR(50),
state CHAR(2),
zip CHAR(10),
email VARCHAR(50),
phone CHAR(15),
fax CHAR(15),
PRIMARY KEY(loginName) )

Notice that the list of column names in Listing above is enclosed in parentheses (one on the first line and one on the last line), and a comma follows each column definition.
Remember not to use any MySQL reserved words for column names. If you do, MySQL gives you an error message that looks
like this:
You have an error in your SQL syntax near ‘order var(20))’ at line 1
Note that this message shows the column definition that it didn’t like and the line where it found the offending definition. However, the message doesn’t tell you much about what the problem is. The error in your SQL syntax that it refers to is the use of the MySQL reserved word order as a column name. After a table has been created, you can query to see it, review its structure, or remove it.

• To see the tables that have been added to a database, use this query: SHOW TABLES
• To see the structure of a table, use this query: DESCRIBE tablename
• To remove any table, use this query: DROP TABLE tablename

Use DROP carefully because it is irreversible. After a table is dropped, it is gone forever. And any data that was in it is gone as well.

How to delete a database?

You can delete any database with this SQL query:
DROP DATABASE databasename
Use DROP carefully because it is irreversible. After a database is dropped, it is gone forever. And any data that was in it is gone as well.

How to create a new database?

To create a new, empty database, use the following SQL query:
CREATE DATABASE databasename where databasename is the name that you give the database. For instance, these two SQL queries create the sample databases used in this book:
CREATE DATABASE PetCatalog
CREATE DATABASE MemberDirectory
Some Web hosting companies don’t allow you to create a new database. You are given one database to use with MySQL, and you can create tables in only this one database. You can try requesting another database, but you need a good reason. MySQL and PHP don’t care that all your tables are in one database instead of organized into databases with meaningful names. It’s just easier for humans to keep track of projects when they’re organized.
To see for yourself that a database was in fact created, use this SQL query:
SHOW DATABASES
After you create an empty database, you can add tables to it.

A quick way to send SQL queries to the MySQL server

When MySQL is installed, a simple, text-based program called mysql (or sometimes the terminal monitor or the monitor) is also installed. Programs that communicate with servers are client software; because this program communicates with the MySQL server, it’s a client. When you enter SQL queries in this client, the response is returned to the client and displayed onscreen. The monitor program can send queries across a network; it doesn’t have to be running on the machine where the database is stored.
To send SQL queries to MySQL by using the mysql client, follow these steps:

• Locate the mysql client. By default, the mysql client program is installed in the subdirectory bin, under the directory where MySQL is installed. In Unix/Linux, the default is /usr/local/mysql/bin or /usr/local/bin. In Windows, the default is c:\Program Files\MySQL\MySQL Server 5.0\bin. However, the client might be installed in a different directory. Or, if you’re not the MySQL administrator, you might not have access to the mysql client. If you don’t know where MySQL is installed or can’t run the client, ask the MySQL administrator to put the client somewhere where you can run it or to give you a copy that you can put on your own computer.
• Start the client. In Unix and Linux, type the path/filename (for example, /usr/local/mysql/bin/ mysql). In Windows, open a command prompt window and then type the path\filename (for example, c:\ Program Files\MySQL\MySQL Server 5.0\bin\mysql). This command will start the client if you don’t need to use an account name or a password. If you need to enter an account or a password or both, use the following parameters:
• u user: user is your MySQL account name.
• p: This parameter prompts you for the password for your MySQL account.

For instance, if you’re in the directory where the mysql client is located, the command might look like this:

mysql -u root -p

• If you’re starting the mysql client to access a database across the network, use the following parameter after the mysql command:
• h host: host is the name of the machine where MySQL is located.

For instance, if you’re in the directory where the mysql client is located, the command might look like this:
mysql -h mysqlhost.mycompany.com -u root -p
Press Enter after typing the command.

• Enter your password when prompted for it. The mysql client starts, and you see something similar to this: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 459 to server version: 5.0.15 Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer. mysql>
• Select the database that you want to use. At the mysql prompt, type the following: use databasename Use the name of the database that you want to query.
• At the mysql prompt, type your SQL query followed by a semicolon (;), and then press the Enter key. The mysql client continues to prompt for input and does not execute the query until you enter a semicolon. The response to the query is displayed onscreen.
• To leave the mysql client, type quit at the prompt and then press the Enter key.

Building SQL queries

SQL (Structured Query Language) is the computer language that you use to communicate with MySQL. SQL is almost English; it is made up largely of English words, put together into strings of words that sound similar to English sentences. In general (fortunately), you don’t need to understand any arcane technical language to write SQL queries that work. The first word of each query is its name, which is an action word (a verb) that tells MySQL what you want to do. The queries that I discuss in this chapter are CREATE, DROP, ALTER, SHOW, INSERT, LOAD, SELECT, UPDATE, and DELETE. This basic vocabulary is sufficient to create — and interact with —databases on Web sites.

The query name is followed by words and phrases — some required and some optional — that tell MySQL how to perform the action. For instance, you always need to tell MySQL what to create, and you always need to tell it which table to insert data into or to select data from.
The following is a typical SQL query. As you can see, it uses English words:

SELECT lastName FROM Member

This query retrieves all the last names stored in the table named Member.
More complicated queries, such as the following, are less English-like:

SELECT lastName,firstName FROM Member WHERE state=”CA” AND city=”Fresno” ORDER BY lastName

This query retrieves all the last names and first names of members who live in Fresno and then puts them in alphabetical order by last name. This query is less English-like but still pretty clear.
Here are some general points to keep in mind when constructing an SQL query, as illustrated in the preceding sample query:

• Capitalization: In this book, I put SQL language words in all caps; items of variable information (such as column names) are usually given labels that are all or mostly lowercase letters. I did this to make it easier for you to read — not because MySQL needs this format. The case of the SQL words doesn’t matter; for example, select is the same as SELECT, and from is the same as FROM, as far as MySQL is concerned. On the other hand, the case of the table names, column names, and other variable information does matter if your operating system is Unix or Linux. When using Unix or Linux, MySQL needs to match the column names exactly, so the case for the column names has to be correct — for example, lastname is not the same as lastName. Windows, however, isn’t as picky as Unix and Linux; from its point of view, lastname and lastName are the same.
• Spacing: SQL words must be separated by one or more spaces. It doesn’t matter how many spaces you use; you could just as well use 20 spaces or just 1 space. SQL also doesn’t pay any attention to the end of the line. You can start a new line at any point in the SQL statement or write the entire statement on one line.
• Quotes: Notice that CA and Fresno are enclosed in double quotes (“) in the preceding query. CA and Fresno are series of characters called text strings, or character strings. You are asking MySQL to compare the text strings in the SQL query with the text strings already stored in the database. When you compare numbers (such as integers) stored in numeric columns, you don’t enclose the numbers in quotes.

Communicating with MySQL

The MySQL server is the manager of your database:

• It creates new databases.
• It knows where the databases are stored.
• It stores and retrieves information, guided by the requests, or queries, that it receives.

To make a request that MySQL can understand, you build an SQL query and send it to the MySQL server.

Writing the programs

Your programs perform the tasks for your Web database application. They create the display that the user sees in the browser window. They make your application interactive by accepting and processing information typed in the browser window by the user. They store information in the database and get information out of the database. The database is useless unless you can move data in and out of it.

The plan that you develop outlines the programs that you need to write. In general, each task in your plan calls for a program. If your plan says that your application will display a form, you need a program that displays a form. If your plan says that your application will store the data from a form, you need a program that gets the data from the form and puts it in the database.

The PHP language was developed specifically to write interactive Web applications. It has the built-in functionality needed to make writing application programs as painless as possible. Methods were included in the language specifically to access data from forms, to put data into a MySQL database, and to get data from a MySQL database.

Building the database

Building the database means turning the paper database design into a working database. Building the database is independent of the PHP programs that your application uses to interact with the database. The database can be accessed using programming languages other than PHP, such as Perl, C, or Java. The database stands on its own to hold the data. You should build the database before writing the PHP programs. The PHP programs are written to move data in and out of the database, so you can’t develop and test them until the database is available. The database design names the database and defines the tables that make up the database. To build the database, you communicate with MySQL by using the SQL language. You tell MySQL to create the database and to add tables to the database. You tell MySQL how to organize the data tables and what format to use to store the data.

Date, time and enumeration data

A third common type of data is date and time data. Data stored as a date can be displayed in a variety of date formats. It can also be used to determine the length of time between two dates or two times — or between a specific date or time and some arbitrary date or time.

Sometimes data can have only a limited number of values. For example, the only possible values for a column might be yes or no. MySQL provides a data type called enumeration for use with this type of data. You tell MySQL what values can be stored in the column (for example, yes, no), and MySQL will not store any other values in the column.

What is numerical data?

Another common type of data is numerical data — data that is stored as a number. Decimal numbers (for example, 10.5, 2.34567, 23456.7) can be stored as well as integers (for example, 1, 2, 248). When data is stored as a number, it can be used in numerical operations, such as adding, subtracting, and squaring. If data isn’t used for numerical operations, however, storing it as a character string is better because the programmer will be using it as a character string. No conversion is required. For example, you probably won’t want to add the digits in the users’ phone numbers, so phone numbers should be stored as character strings.

MySQL stores positive and negative numbers, but you can tell MySQL to store only positive numbers. If your data is never negative, store the data as unsigned (without using a + or – sign before the number). For example, a city population or the number of pages in a document can never be negative. MySQL provides a specific type of numeric column called an auto-increment column. This type of column is automatically filled with a sequential number when no specific number is provided. For example, when a table row is added with 5 in the auto-increment column, the next row is automatically assigned 6 in the column, unless a different number is specified. Auto-increment columns are useful when unique numbers are needed, such as a product number or an order number.

What is character data?

The most common type of data is character data — data that is stored as strings of characters and can be manipulated only in strings. Most of the information that you store will be character data, such as customer name, address, phone, and pet description. Character data can be moved and printed. Two character strings can be put together (concatenated), a substring can be selected from a longer string, and one string can be substituted for another. Character data can be stored in a fixed-length or variable-length format.

• Fixed-length format: In this format, MySQL reserves a fixed space for the data. If the data is longer than the fixed length, only the characters that fit are stored — the remaining characters on the end are not stored. If the string is shorter than the fixed length, the extra spaces are left empty and wasted.
• Variable-length format: In this format, MySQL stores the string in a field that is the same length as the string. You specify a string length, but if the string is shorter than the specified length, MySQL uses only the space required rather than leaving the extra space empty. If the string is longer than the space specified, the extra characters are not stored.

If a character string length varies only a little, use the fixed-length format. For example, a length of 10 works for all zip codes, including those with the zip+4 number. If the zip code does not include the zip+4 number, only five spaces are left empty. However, if your character string can vary more than a few characters, use a variable-length format to save space. For example, your pet description might be Small bat or might run to several lines of description. It would be better to store this description in a variable-length format.

how to design member registration process for your web site?

You create the following list of information that you want to store when customers register for the Members Only section of your Web site:

• Member name
• Member address
• Member phone number
• Member fax number
• Member e-mail address

In addition, you would like to collect the date when the member registers and track how often the member goes into the Members Only section. You design the Members Only database by following the steps presented in the “Organizing data in tables” section, earlier:

• Name your database. The database for the Members Only section may be named MemberDirectory or other.
• Identify the objects. The information list is
• Member name
• Member address
• Member phone number
• Member fax number
• Member e-mail address
• Member registration date
• Member logins

All this information pertains to members, so the only object for this list is member.

• Define and name a table for each object. The MemberDirectory database needs a table called Member.
• Identify the attributes for each object. Look at the information list in detail:
• Member name: Two attributes: first name and last name.
• Member address: Four attributes: street address, city, state, and zip code. Currently, you have pet stores only in the United States, so you can assume that the member address is an address in the U.S. mailing address format.
• Member phone number: One attribute.
• Member fax number: One attribute.
• Member e-mail address: One attribute.
• Member registration date: One attribute.

Several pieces of information are related to member logins:

• Logging in to the Members Only section requires a login name and a password. These two items need to be stored in the database.
• The easiest way to keep track of member logins is to store the date and time when the user logged into the Members Only section. Because each member can have many logins, many dates and times for logins need to be stored. Therefore, rather than defining the login time as an attribute of the member, define login as an object, related to the member, but requiring its own table.

The added table is named Login. The attribute of a login object is its login time (the time includes the date).

• Define and name the columns. The Member table has one row for each member. The columns for the

Member table are

• loginName
  
• password
  
• createDate
  
• firstName
  
• lastName
  
• street
  
• city
• state
• zip
• email
• phone
• fax

The Login table has one row for each login: that is, each time a member logs into the Members Only section. It has the following columns:

• loginName: The login name of the member who logged in. This is the column that links this table to the Member table. This value is unique in the Member table but not unique in this table.
• loginTime: The date and time of login.
• Identify the primary key.
• The primary key for the Member table is loginName. Therefore, loginName must be unique.
• The primary key for the Login table is both loginName and loginTime together.
• Define the defaults. No defaults are defined for either table.
• Identify columns with required data. The following columns should never be allowed to be empty:
• loginName
• password
• loginTime

These columns are the primary key columns. A row without these values should never be allowed in the tables.

How to create relationships between tables?

Some tables in a database are related. Most often, a row in one table is related to several rows in another table. A column is needed to connect the related rows in different tables. In many cases, you include a column in one table to hold data that matches data in the primary key column of another table.

A common application that needs a database with two related tables is a customer order application. For example, one table contains the customer information, such as name, address, and phone number. Each customer can have from zero to many orders. You could store the order information in the table with the customer information, but a new row would be created each time that the customer placed an order, and each new row would contain all the customer’s information. It would be much more efficient to store the orders in a separate table, named perhaps CustomerOrder. (You can’t name the table Order because that is a reserved word.) The CustomerOrder table would have a column that contains the primary key from a row in the Customer table so that the order is related to the correct row of the Customer table.

How to organize data in tables?

RDBMS tables are organized like other tables that you’re used to — in rows and columns. The place where a particular row and column intersect, the individual cell, is a field.
The focus of each table is an object (a thing) that you want to store information about. Here are some examples of objects:

Customers Shapes Rooms
Companies Projects Computers
Cities Products Documents
Books Animals Weeks

You create a table for each object. The table name should clearly identify the objects that it contains with a descriptive word or term. The name must be a character string, containing letters, numbers, underscores, or dollar signs, with no spaces in it. It’s customary to name the table in the singular. Thus, a name for a table of customers might be Customer, and a table containing customer orders might be named CustomerOrder. Uppercase and lowercase is significant on Linux and Unix but not on Windows: CustomerOrder and Customerorder are the same to Windows — but not to Linux or Unix.

In database talk, an object is an entity, and an entity has attributes. In the table, each row represents an entity, and the columns contain the attributes of each entity. For example, in a table of customers, each row contains information for a single customer. Some of the attributes contained in the columns might be first name, last name, phone number, and age.
Here are the steps for organizing your data into tables:

• Name your database. Assign a name to the database for your application. For instance, a database containing information about households in a neighborhood might be named HouseholdDirectory.
• Identify the objects. Look at the list of information that you want to store in the database (as discussed in the section, “Choosing the data,” earlier in this chapter). Analyze your list and identify the objects. For instance, the HouseholdDirectory database might need to store the following:
• Name of each family member
• Address of the house
• Phone number
• Age of each household member
• Favorite breakfast cereal of each household member
• When you analyze this list carefully, you realize that you’re storing information about two objects: the household and the household members. That is, the address and phone number are for the household in general, but the name, age, and favorite cereal are for a particular household member.
• Define and name a table for each object. For instance, the HouseholdDirectory database needs a table called Household and a table called HouseholdMember.
• Identify the attributes for each object. Analyze your information list and identify the attributes you need to store for each object. Break the information to be stored into its smallest reasonable pieces. For example, when storing the name of a person in a table, you can break the name into first name and last name. Doing this enables you to sort by the last name, which would be more difficult if the first and last name were stored together. You can even break down the name into first name, middle name, and last name, although not many applications need to use the middle name separately.
• Define and name columns for each separate attribute that you identified in Step 4. Give each column a name that clearly identifies the information in that column. The column names should be one word, with no spaces. For example, you might have columns named firstName and lastName or first_name and last_name.
• Some words are reserved by MySQL and SQL for their own use and can’t be used as column names. The words are currently used in SQL statements or are reserved for future use. For example, ADD, ALL, AND, CREATE, DROP, GROUP, ORDER, RETURN, SELECT, SET, TABLE, USE, WHERE, and many, many more can’t be used as column names. For a complete list of reserved words, see the online MySQL manual at www.mysql.com/doc/en/Reserved_words.html.
• Identify the primary key. Each row in a table needs a unique identifier. No two rows in a table should be exactly the same. When you design your table, you decide which column holds the unique identifier, called the primary key. The primary key can be more than one column combined. In many cases, your object attributes will not have a unique identifier. For example, a customer table might not have a unique identifier because two customers can have the same name. When there is no unique identifier column, you need to add a column specifically to be the primary key. Frequently, a column with a sequence number is used for this purpose.
  
• Define the defaults. You can define a default that MySQL will assign to a field when no data is entered into the field. A default is not required but is often useful. For example, if your application stores an address that includes a country, you can specify US as the default. If the user does not type a country, US will be entered.
• Identify columns that require data. You can specify that certain columns are not allowed to be empty (also called NULL). For instance, the column containing your primary key can’t be empty. That means that MySQL will not create the row and will return an error message if no value is stored in the column. The value can be a blank space or an empty string (for example, “”), but some value must be stored in the column. Other columns, in addition to the primary key, can be set to require data.

Well-designed databases store each piece of information in only one place. Storing it in more than one place is inefficient and creates problems if information needs to be changed. If you change information in one place but forget to change it in another place, your database can have serious problems. If you find that you’re storing the same data in several rows, you probably need to reorganize your tables. For example, suppose you’re storing data about books, including the publisher’s address. When you enter the data, you realize that you’re entering the same publisher’s address in many rows.

A more efficient way to store this data would be to store the book information in one table and the book publisher information in a separate table. You can define two tables: Book and BookPublisher. In the Book table, you would have the columns title, author, pub_date, and price. In the BookPublisher table, you would have columns such as name, streetAddress, and city.

Data choosing tips when building database

First, you must identify what information belongs in your database. Look at the list of tasks that you want the application to perform and determine what information you need to complete each of those tasks.
Here are a few examples:

• An online catalog needs a database containing product information. _ An online order application needs a database that can hold customer information and order information.
• A travel Web site needs a database with information on destinations, reservations, fares, schedules, and so on.

In many cases, your application might include a task that collects information from the user. You’ll have to balance your urge to collect all the potentially useful information that you can think of against your users’ reluctance to give out personal information — as well as their avoidance of forms that look too time-consuming. One compromise is to ask for some optional information. Users who don’t mind can enter it, but users who object can leave it blank. Another possibility is to offer an incentive: The longer the form, the stronger the incentive that you’ll need to motivate the user to fill out the form. A user might be willing to fill out a short form to enter a sweepstakes that offers two sneak-preview movie tickets for a prize. But if the form is long and complicated, the prize needs to be more valuable, such as a free trip to California and a tour of a Hollywood movie studio.

In the first example application, your customers search the online catalog for information on pets that they might want to buy. You want customers to see information that will motivate them to buy a pet. The information that you want to have available in the database for the customer to see is as follows:

• The name of the pet (for example, poodle or unicorn)
• A description of the pet
• A picture of the pet
• The cost of the pet

In the second example application, the Members Only section, you want to store information about registered members. The information that you want to store in the database is as follows:

• Member name
• Member address
• Member phone number
• Member fax number
• Member e-mail address

Take the time to develop a comprehensive list of the information you need to store in your database. Although you can change and add information to your database after it’s developed, including the information from the beginning is easier. Also, if you add information to the database later — after it’s in use —the first users in the database will have incomplete information. For example, if you change your form so that it now asks for the user’s age, you won’t have the age for the people who have already filled out the form and are already in the database.

Writing it down

Write your plan down. You will hear this often from me. I speak from the painful experience of not writing it down. When you develop your plan, it’s foremost in your mind and perfectly clear. But in a few short weeks, you will be astonished to discover that it has gone absolutely hazy while your attention was on other pressing issues. Or you want to make some changes in the application a year from now and won’t remember exactly how the application was designed. Or you’re working with a partner to develop an application and you discover that your partner misunderstood your verbal explanation and developed functions for the application that don’t fit in your plan. You can avoid these types of problems by writing everything down.

Leaving room for expansion

One certainty about your Web application is that it will change over time. Down the line, you might think of new functions for it or just simply want to change something about it. Or maybe Web site software improves so that your Web application can do things that it couldn’t do when you first put it up. Whatever the reason, your Web site will change. When you plan your application, you need to keep future changes in mind.

You can design your application in steps, taking planned changes into account. You can develop a plan in which you build an application today that meets your most immediate needs and make it available as soon as it’s ready. Your plan can include adding functions to the application as quickly as you can develop them. For example, you can build a product catalog and publish it on your Web site as soon as it’s ready. You can then begin work on an online ordering function for the Web site, which you will add when it’s ready.

You can’t necessarily foresee all the functions that you might want in your application. For instance, you might design your travel Web site with sections for all possible destinations today, but the future could surprise you. Trips to Mars? Alpha Centauri? An alternate universe? Plan your application with the flexibility needed to add functionality in the future.

How to make the site easy to use?

In addition to planning what your Web application is going to do, you need to consider how it is going to do it. Making your application easy to use is important: If customers can’t find your products, they aren’t going to buy them. And if customers can’t find the information they need in a short time, they will look elsewhere. On the Web, customers can easily go elsewhere. Making your application easy to use is usability engineering. Web usability includes such issues as

• Navigation: What is on your site and where it is located should be immediately obvious to a user.
• Graphics: Graphics make your site attractive, but graphic files can be slow to display.
• Access: Some design decisions can make your application accessible or not accessible to users who have disabilities such as impaired vision.
• Browsers: Different browsers (even different versions of the same browser) can display the same HTML file differently.

Web usability is a large and important subject, and delving into the topic more deeply is beyond the scope of this book. But fear not, you can find lots of helpful information on Web usability on — you guessed it — the Web. Be sure to check out the Web sites of usability experts Jakob Nielsen (www.useit.com) and Jared Spool (www.uie.com). Vincent Flanders also has a fun site full of helpful information about Web design at WebPagesThatSuck.com.

Taking the user into consideration when planning web application

Identifying what you want your Web database application to do is only one aspect of planning. You must also consider what your users will want from it. For example, say your goal is to gather a list of names and addresses for marketing purposes. Will customers be willing to give up that information? Your application needs to fulfill a purpose for the users as well as for yourself. Otherwise, they’ll just ignore it. Before users will be willing to give you their names and addresses, for example, they need to perceive that they will benefit from giving you this information. Here are a few examples of why users might be willing to register their names and addresses at your site:

• To receive a newsletter: To be perceived as valuable, the newsletter should cover an industry related to your products. It should offer news and spot trends — and not just serve as marketing material about your products.
• To enter a sweepstakes for a nice prize: Who can turn down a chance to win an all-expense-paid vacation to Hawaii or a brand-new SUV?
• To receive special discounts: For example, you can periodically e-mail special discount opportunities to customers.
• To be notified about new products or product upgrades when they become available: For example, customers might be interested in being notified when a software update is available for downloading.
• To get access to valuable information: For instance, you must register at The New York Times Web site to gain access to its articles online.

Now add the customer tasks to your list of tasks that you want the application to perform. For example, consider this list of tasks that you identified for setting up an online retailer:

• Provide a form for customers to fill out
• Store the customer information in a database

If you take the customer’s viewpoint into account, the list expands a bit:

• Present a description of the advantages customers receive by registering with the site
• Provide a form for customers to fill out
• Add customers’ e-mail addresses to the newsletter distribution list
• Store the customer information in a database

After you have a list of tasks that you want and tasks that your users want, you have a plan for a Web application that is worth your time to develop and worth your users’ time to use.

How to Identify what you want from the application?

The first step in the planning phase is to identify exactly why you’re developing your application and what you want from it. For example, your main purpose might be to

• Collect names and addresses from users so that you can develop a customer list
• Deliver information about your products to users, as in a customer catalog
• Sell products online
• Provide technical support to people who already own your product

After you clearly identify the general purpose of your application, make a list of exactly what you want that application to do. For instance, if your goal is to develop a database of customer names and addresses for marketing purposes, the application’s list of required tasks is fairly short:

• Provide a form for customers to fill out
• Store the customer information in a database

If your goal is to sell products online, the list is a little longer:

• Provide information about your products to the customer
• Motivate the customer to buy the product
• Provide a way for the customer to order the product online
• Provide a method for the customer to pay for the product online
• Validate the payment so you know that you’ll actually get the money
• Send the order to the person responsible for filling the order and sending the product to the customer.

At this point in the planning process, the tasks that you want your application to perform are still pretty general. You can accomplish each of these tasks in many different ways. So now you need to examine the tasks closely and detail exactly how the application will accomplish them. For instance, if your goal is to sell products online, you might expand the preceding list like this:

• Provide information about products to the customer.
• Display a list of product categories. Each category is a link.
• When the customer clicks a category link, the list of products in that category is displayed. Each product name is a link.
• When a customer clicks a product link, the description of the product is displayed.
• Motivate the customer to buy the product.
• Provide well-written descriptions of the products that communicate their obviously superior qualities.
• Use flattering pictures of the products.
• Make color product brochures available online.
• Offer quantity discounts.
• Provide a way for customers to order the product online.
• Provide a button that customers can click to indicate their intention to buy the product.
• Provide a form that collects necessary information about the product the customer is ordering, such as size and color.
• Provide forms for customers to enter shipping and billing addresses.
• Compute and display the total cost for all items in the order.
• Compute and display the shipping costs.
• Compute and display the sales tax.
• Provide a method for customers to pay for the product online.
• Provide a button that customers can click to pay with a credit card.
• Display a form that collects customers’ credit card information.
• Validate the payment so you know that you’ll actually get the money. The usual method is to send the customer’s credit card information to a credit card processing service.
• Send the order to the person responsible for filling the order and sending the product to the customer.

E-mailing order information to the shipping department should do it. At this point, you should have a fairly clear idea of what you want from your Web database application. However, this doesn’t mean that your goals can’t change. In fact, your goals are likely to change as you develop your Web database application and discover new possibilities. At the onset of the project, start with as comprehensive a plan as possible to keep you focused.