Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

just jump in, creating tables and fields for their current needs without planning for the future. This technique
leads to a poor structure from which retrieving a single tidbit of information is like getting a tooth pulled, and
scaling the poorly-constructed database to fit the needs of the company is a historic event.

Creating a database is a lot like building a house. The builders do not build a house without a plan. An

architect comes up with the plan and gives it to the builder. The builder takes the plan and builds the

house. The builder pays special attention to the foundation of the house, because without a strong

foundation the house will fall. These same basic principles apply to building a database.

You are going to play both roles, the architect and the builder. As the architect, you will come up with

the blueprint of the database. You must decide what information you are going to store and track. You

must also define the relationships that exist between the tables you are going to build. This is vital to a

good, solid relational database.

In the role of the database builder, you will enter the SQL statements that actually create the database.

You will have to know what data types to use to store the data efficiently and correctly. This is where the

foundation of the database is built. Knowing what types to use and when to use them, as well as

building the proper relationships, will help create a solid foundation for your database.

To guide you along the process, I've established a series of steps to help ensure that the design

process is performed correctly and thoroughly (see Figure 3.1). The first step is to define the current

business process or, in some cases, invent the process. The next step is to define the business objects.

After you define the objects, you define the business rules as they relate to these objects. The fourth

step is to draw or lay out the database. This helps with the next step, which is to define the table

relationships. After the relationships have been defined, you must define the type of data that you are

going to store for each field. After all these steps have been taken and you have thoroughly reviewed

your work, you can create the database.

Figure 3.1 The database design process.

Defining the Business Process

The first step in designing a database is to gain a working knowledge of the current business process. A
business process is the way a business performs its duties to meet its goals. For example, an online
bookstore might have the following business process:

1. An order is placed for a book by a customer via a Web-based order form.


2. The credit card is verified.


3. The book is deducted from the inventory and the order is placed to the shipping
   department.


4. The shipping department packages the product, verifies the address, and ships
   the package out.

In some cases, you will be updating an existing computer-based process, and in others you will be

creating a computer-based process based on a paper process.

There are many different techniques to help you gain an understanding of the business process. The

most helpful is to interview the people who work with the system everyday. These people should know

the inner workings of the process. You may have to interview more than one person to gain a complete

and total understanding of the process. How to interview and what questions to ask goes beyond the