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.