How to Choose the Data for Your MySQL Database - dummies

How to Choose the Data for Your MySQL Database

By Steve Suehring, Janet Valade

To design a MySQL database, you first must identify what information belongs in it. The database must contain the data needed for the website to perform its purpose. 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 website 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. For instance, customers who buy products from a website must provide their address, phone number, credit card information, and other data in order to complete the order. The information must be saved at least until the order is filled.

Often, the website retains the customer information to facilitate future orders so the customer doesn’t need to retype the information when placing the next order. The information also provides marketing opportunities to the business operating the website, such as sending marketing offers or newsletters to customers.

A customer database might collect the following customer information:

  • Name

  • Address

  • Phone number

  • Fax number

  • E-mail address

You have to balance your urge to collect all the potentially useful information 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 that information, but users who object can leave that portion of the form blank. You can also offer an incentive: The longer the form, the stronger the incentive you need to motivate the user to fill out the form.

Here’s an example: A user might be willing to fill out a short form to enter a sweepstakes that offers two sneak-preview movie tickets as a prize, but if the form is long and complicated, the prize needs to be more valuable, such as a chance to win a trip to Hollywood.

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 you develop it, including the information from the beginning is easier, and you might be able to avoid the extra work of changing the database later.

Also, if you add information to the database later — after that database is in use — the first users in the database have incomplete information. For example, if you change your form so that it now asks for the user’s age, you don’t have the age for the people who already filled out the form and are already in the database.