A Five-Step Design Method

Figure 33-2 is a version of the design method that is modified especially for use with Access. This is a top-down approach, starting with the overall system design and ending with the forms design, and it consists of five steps.

These five design steps, along with the database system illustrated by the examples in this book, teach a great deal about Access and provide a great foundation for creating database applications—including tables, queries, forms, data pages, reports, macros, and simple VBA (Visual Basic for Applications) modules.

The time you spend on each step depends entirely on the circumstances of the database you’re building. For example, sometimes the users give you an example of a report they want printed from their Access database, and the sources of data on the report are so obvious that designing the report takes a few minutes. Other times, particularly when the users’ requirements are complex, or the business processes supported by the application require a great deal of research, you may spend many days on Step 1.

As you read through each step of the design process, always look at the design in terms of outputs and inputs. Although you see actual components of the system (cars, buyers, sellers, and transactions), remember that the focus of this chapter is how to design each step. As you watch the Access Auto Auctions system being designed, pay attention to the design process, not the actual system.

Step 1: The overall design—from concept to reality

All software developers face similar problems, the first of which is determining how to meet the needs of the end user. It’s important to understand the overall requirements before zeroing in on the details.

The five-step design method shown in Figure 33-2 helps you to create the system that you need, at an affordable price (measured in time or dollars). The Access Auto Auctions database, for example, allows the client to sell items (vehicles and parts) to customers. The Access Auto Auctions database automates the following tasks:

• Entering and maintaining contact information for customers and sellers (name, address, and financial history)

• Entering and maintaining sales information (sales date; payment method; total amount, including tax; buyer ID; and other fields)

• Entering and maintaining sales line item information (details of items actually purchased)

• Viewing information from all the tables (sales, contacts, sales line items purchased, and payment information)

• Asking all types of questions about the information in the database

• Producing a current contacts directory

• Producing a monthly invoice report

• Producing a customer sales history

• Producing mailing labels and mail-merge reports

These nine tasks that the Access Auto Auctions automates have been expressed by the client. You may need to consider other tasks as you start the design process.

Most of the information that is necessary to build the system comes from the eventual users. This means that you need to sit down with them and learn how the existing process works. To accomplish this you need to do a thorough needs analysis of the existing system and how you might automate it.

One way to accomplish this is to prepare a series of questions that give insight to the client’s business and how the client uses his data. For example, when considering automating an auto auction business, you may consider asking these questions:

•	What reports and forms are currently used?
•	How are sales, customer, contacts, and other records currently stored?
•	How are billings processed?

As you ask these questions and others, the client will probably remember other things about his business that you should know.

A walkthrough of the existing process is also necessary to get a “feel” for the business. Most likely, you’ll have to go back several times to observe the existing process and how the employees work.

When you prepare to follow the remaining steps, keep the client involved—let him know what you’re doing and ask for his input as to what you want to accomplish, making sure it is within the scope of his needs.

Step 2: Report design

Although it may seem odd to start with reports, in many cases users are more interested in the printed output from a database than they are in any other aspect of the application. Reports often include virtually every bit of data managed by an application. Because reports tend to be comprehensive, reports are often the best way to gather important information about a database’s requirements. In the case of the Access Auto Auctions database, the printed reports contain detailed and summarized versions of most all the data in the database.

After you’ve defined the Access Auto Auctions’ overall systems in terms of what must be accomplished, you can begin report design.

When you see the reports that you will create in this section, you may wonder, “Which comes first—the chicken or the egg?” Does the report layout come first, or do you first determine the data items and text that make up the report? Actually, these items are considered at the same time.

It isn’t important how you lay out the fields in a report. The more time you take now, however, the easier it will be to construct the report. Some people go so far as to place gridlines on the report so that they will know the exact location they want each bit of data to occupy. In this example, you can just do it visually.

The reports in Figures 33-3 and 33-4 were created with two different purposes. The report in Figure 33-3 displays information about an individual contact (buyer, seller, or both). In contrast, the report in Figure 33-4 is an invoice with billing and customer information. Both of these reports were based on the type of information they use. The design and layout of each report is driven by the report’s purpose and the data it contains.

Step 3: Data design: What fields are required?

The next step in the design phase is to take an inventory of all the information or data fields that are needed by the reports. One of the best methods is to list the data items in each report. As you do so, take careful note of items that are included in more than one report. Make sure that you keep the same name for a data item that is in more than one report because the data item is really the same item.

Another method is to see whether you can separate the data items into some logical arrangement. Later, these data items are grouped into table structures and then mapped onto data-entry screens (forms). You should enter customer data (buyers and sellers), for example, as part of a contact table process, not as part of a sales entry.

Determining contact information

First, look at each report you have reviewed or attempted to make for the Access Auto Auctions system. For this system, start with the customer data and list the data items, as shown in Table 33-1.

Table 33-1. Customer-Related Data Items Found in the Reports

Contacts Report	Invoice Report
Customer Name	Customer Name
Street	Street
City	City
State	State
ZIP Code	ZIP Code
Phone Number	Phone Number
Type of Customer
E-Mail Address
Web Site Information
Contact Log Information (four fields)
Discount Rate
Customer Since
Last Sales Date
Sales Tax Rate
Credit Information (four fields)

As you can see by comparing the type of contact (customer) information needed for each report, there are many common fields. Most of the data fields pertaining to the customer are found in both reports. Table 33-1 shows only some of the fields that are used in each reportthose related to customer information. Fields appearing on both reports appear on the same rows in the table, which allows you to see more easily which items are in which reports. You can look across a row instead of looking for the same names in both reports. Because the related row and the field names are the same, you can easily make sure that you have all the data items. Although locating items easily is not critical for this small database, it becomes very important when you have to deal with large tables containing many fields.

Determining sales information

After extracting the customer data, you can move on to the sales data. In this case, you need to analyze only the Invoice report for data items that are specific to the sales. Table 33-2 lists the fields in the report that contain information about the sales.

Table 33-2. Sales Data Items Found in the Reports

Individual Invoice Report	Line Item Data
Invoice Number
Sales Date
Invoice Date
Payment Method
Payment Salesperson
Discount (overall for sale)
Tax Location
Tax Rate
Product Purchased (multiple lines)	Product Purchased
Quantity Purchased (multiple lines)	Quantity Purchased
Description of Item Purchased (multiple lines)	Description of Item Purchased
Price of Item (multiple lines)	Price of Item
Discount for each item (multiple lines)	Discount for Each Item
Taxable? (multiple lines)	Taxable?
Payment Type (multiple lines)
Payment Date (multiple lines)
Payment Amount (multiple lines)
Credit Card Number (multiple lines)
Expiration Date (multiple lines)

As you can see when you examine the type of sales information needed for the report, a couple of items (fields) are repeating (for example, the Product Purchased, Number of Items Purchased, and Price of Item fields). Each invoice can have multiple items, and each of these items needs the same type of information—number ordered and price per item. Each sales invoice will probably have more than one item that is sold and being invoiced. Also, each invoice may include partial payments, and it is possible that this payment information will have multiple lines of payment information, so these repeating items can be put into their own grouping.

Determining line item information

You can take all the individual items that you found in the sales information group in the preceding section and extract them to their own group for the invoice report. Table 33-2 shows the information related to each line item.

Looking back at the report in Figure 33-4, you can see that the data from Table 33-2 doesn’t list the calculated field amount, but you can re-create it easily in the report.

Step 4: Table design

Now for the difficult part: You must determine what fields are needed for the tables that make up the reports. When you examine the multitude of fields and calculations that make up the many documents you have, you begin to see which fields belong to the various tables in the database. (You already did much of the preliminary work by arranging the fields into logical groups.) For now, include every field you extracted. You will need to add others later (for various reasons), although certain fields won’t appear in any table.

It is important to understand that it isn’t necessary to add every little bit of data into the database’s tables. For instance, users may express a desire to add vacation and other out-of-office days to the database to make it easy to know which employees are available on a particular day. However, it is very easy to burden an application’s initial design by incorporating too many ideas during the initial development phases. Because Access tables are so easy to modify later on, it is probably best to put aside noncritical items until the initial design is complete. Generally speaking, it’s not difficult to accommodate user requests after the database development project is under way.

After you’ve used each report to display all the data, it’s time to consolidate the data by purpose (for example, grouped into logical groups) and then compare the data across those functions. To do this step, first you look at the contact information and combine all of its different fields to create one set of data items. Then you do the same thing for the sales information and the line item information. Table 33-3 compares data items from these three groups of information.

Table 33-3. Comparing the Data Items from the Contact Information, Sales Information, and Line Item Information

Contacts Data	Invoice Data	Line Items
Customer Name	Invoice Number	Product Purchased
Street	Sales Date	Quantity Purchased
City	Invoice Date	Description of Item Purchased
State	Payment Method	Price of Item
ZIP Code	Payment Salesperson	Discount for Each Item
Phone Numbers (two fields)	Discount (overall for this sale)	Taxable?
Type of Customer	Tax Location
E-Mail Address	Tax Rate
Web Site Information	Payment Type (multiple lines)
Contact Log Information (four fields)	Payment Date (multiple lines)
Discount Rate	Payment Amount (multiple lines)
Customer Since	Credit Card Number (multiple lines)
Last Sales Date	Expiration Date (multiple lines)
Sales Tax Rate
Credit Information (four fields)

Consolidating and comparing data is a good way to start creating the individual table definitions for Access Auto Auctions, but you have much more to do.

As you learn more about how to perform a data design, you also learn that the contacts data must be split into two groups. Some of these items are used only once for a contact, while other items may have multiple entries. An example is the Contact Log information. Each contact may have multiple log items recorded in the database. This is also true for the Sales column—the payment information can have multiple lines of information.

It is necessary to further break these types of information into their own columns, thus separating all related types of items into their own columns—an example of the normalization part of the design process. For example, one customer can have multiple contacts with the company. One customer may make multiple payments toward a single sale. Of course, we’ve already broken the data into three categories above: contacts, invoices, and sales line items.

Keep in mind that one customer may have multiple invoices, and each invoice may have multiple line items on it. The contact category represents customer (buyer or seller) information, the invoice category contains information about individual sales, and the line items category contains information about each invoice. Notice that these three columns are all related; for example, one customer can have multiple invoices and each invoice may require multiple detail lines (line items).

The relationships between tables can be different. For example, each sales invoice has one and only one customer, while each customer may have multiple sales. A similar relationship exists between the sales invoice and the line items of the invoice.

Assuming that the three groupings represent the main three tables of your system, less additional fields, you need to link tables together. This step, of course, means adding table relationships to the database design.

Database table relationships require a unique field in both tables involved in a relationship. Without a unique identifier in each table, the database engine is unable to properly join and extract related data.

None of the tables in our design has a unique identifier, which means that you need to add at least one more field to each table to serve as the anchor for a relationship to other tables. For example, you could add a ContactID field to the Contacts table, then add the same field to the Invoice table, and establish a relationship between the tables through the ContactID field in each table. The database engine uses the relationship between the Contacts and Invoices table to link customers with their invoices. Linking tables is done through special fields, known as key fields.

With an understanding of the need for linking one group of fields to another group, you can add the required key fields to each group. Table 33-4 shows two new groups and link fields created for each group of fields. These linking fields, known as primary keys and foreign keys, are used to link these tables together.

Table 33-4. Main Tables with Keys

Contacts Data	Invoice Data	Line Items Data	Contact Log Data	Sales Payment Data
ContactID	InvoiceID	InvoiceID	ContactLogID	InvoiceID
Customer Name	ContactID	Line Number	ContactID	Payment Type
Street	Invoice Number	Product Purchased	Contact Date	Payment Date
City	Sales Date	Quantity Purchased	Contact Notes	Payment Amount
State	Invoice Date	Description of Item Purchased	Follow Up?	Credit Card Number
ZIP Code	Payment Method	Price of Item	Follow-Up Date	Expiration Date
Phone Numbers (two fields)	Payment Salesperson	Discount for Each Item
Type of Customer	Discount (overall for this sale)	Taxable?
E-Mail Address	Tax Location
Web Site Information	Tax Rate
Discount Rate
Customer Since
Last Sales Date
Sales Tax Rate

The field that uniquely identifies each row in a table is called the primary key. The corresponding field in a related table is called the foreign key. In our example, the ContactID field in the Contacts table is a primary key, while the ContactID field in the Invoices table is a foreign key.

Let’s assume a certain record in the Contacts table has 12 in its ContactID field. Any records in the Invoices table with 12 in its ContactID field is “owned” by contact number 12. As you’ll see in Chapters 2 and 3, special rules apply to choosing and managing primary and foreign keys. The notion of primary and foreign keys is the single most important concept behind relational databases. You can read much more about this important concept in Chapters 2 and 3.

With the key fields added to each table, you can now find a field in each table that links it to other tables in the database. For example, Table 33-4 shows a ContactID field in both the Contacts table (where it is the table’s primary key) and the Invoice table (where it is a foreign key).

You have identified the core of the three primary tables for your system, as reflected by the first three columns in Table 33-4. This is the general, or first, cut toward the final table designs. You have also created two additional tables (columns) from fields shown in Table 33-3.

Taking time to properly design your database and the tables contained within it is arguably the most important step in developing a database-oriented application. By designing your database efficiently, you maintain control of the data—eliminating costly data-entry mistakes and limiting your data entry to essential fields.

Although these chapters are not geared toward teaching database theory and all of its nuances, this is a good point to briefly describe the art of database normalization. Normalization is the process of breaking data down into constituent tables. Earlier in this chapter you read about how many Access developers add dissimilar information, such as contacts, invoice data, and invoice line items, into one large table. A large table containing dissimilar data quickly becomes unwieldy and hard to keep updated. Because a contact’s phone number appears in every row containing that customer’s data, multiple updates must be made when the contact’s phone number changes.

Normalization is the process of breaking data into smaller, more manageable tables. Each table defines one and only one entity, such as a contact or an invoice, but not both. The contact and invoice tables are related through a primary key (ContactID in the customers table) and a foreign key (also named ContactID) in the invoices table.

Step 5: Form design: Input

After you’ve created the data and established table relationships, it’s time to design your forms. Forms are made up of the fields that can be entered or viewed in Edit mode. If at all possible, your screens should look much like the forms that you use in a manual system. This setup makes for the most user-friendly system.

When you’re designing forms, you need to place three types of objects onscreen:

• Labels and text box data-entry fields (the fields on Access forms and reports are usually called controls)

• Special controls (multiple-line text boxes, option buttons, list boxes, checkboxes, business graphs, and pictures)

• Graphical objects to visually enhance them (colors, lines, rectangles, and three-dimensional effects)

When designing a form, place your fields (text boxes, checkboxes, list boxes, and radio buttons) just where you want them on the form. Ideally, if the form is being developed from an existing printed form, the Access data-entry form should resemble the printed form. The fields should be in the same relative place on the screen as they are in the printed counterpart.

Labels display messages, titles, or captions. Text boxes provide an area where you can type or display text or numbers that are contained in your database. Checkboxes indicate a condition and are either unchecked or checked (selected). Other types of controls available with Access include list boxes, combo boxes, option buttons, toggle buttons, and option groups.