Summary

   

---

Compiling a Complete List of Fields

The Preliminary Field List

Now that you have completed your analysis of the current database and the interviews with users and management, you can create a preliminary field list . This list represents the organization's fundamental data requirements and constitutes the core set of fields that you'll define in the database. You create the preliminary field list using a two-step process.

Step One: Review and Refine the List of Characteristics

The first step involves reviewing and refining the list of characteristics you compiled throughout the analysis and interview process. As you learned in Chapter 3, a field represents a characteristic of a particular subject; therefore, each item on your list of characteristics will become a field. Before you transform those characteristics into fields, however, you first need to review the list to identify and remove duplicate characteristics.

During the interviews, you identified various characteristics within each participant's responses, compiling them into a list as the interview progressed. There were probably times when you mistakenly added the same characteristic to the list more than once, or unknowingly referred to the same characteristic by two or more different names . As a result, your list of characteristics requires some refinement.

Refining Items with the Same Name

Begin refining your list of characteristics by looking for items with the same name. When you find one or more occurrences of a particular name, determine whether they all represent the same characteristic. Remove all but one occurrence of the name from the list if they do represent the same characteristic; otherwise , determine what each instance of the name represents. You'll often find that a duplicate name represents the same type of characteristic as its original counterpart, but should be associated with a different subject than its counterpart . In this case, you rename the duplicate to reflect how it relates to the appropriate subject.

Assume, for example, that the item "Name" appears three times on your list of characteristics. Your first inclination will probably be to remove two of the occurrences because your current objective is to eliminate duplicate characteristics. However, you should determine whether each instance of "Name" represents a distinct characteristic before you remove it. You can easily make this determination by examining your interview notes; this will help you remember when and why you added the item to the list.

After careful examination, you discover that the first occurrence of "Name" represents a characteristic of the subject "Clients," the second, a characteristic of the subject " Employees ," and the third, a characteristic of the subject "Contacts." You resolve this duplication by renaming each occurrence of "Name" (using the subject as a prefix) to reflect its true meaning. Now you'll have three new characteristics called "Client Name," "Employee Name," and "Contact Name."

Items similar to "Name" commonly appear on a list of characteristics, and you must address them in the same manner. You'll commonly see one or more occurrences of items such as "Address," "City," "State," "Zip Code," and "Phone Number," and you can refer to them collectively as generic items . The point here is that you must rename each instance of a generic item to reflect its true relationship to a particular subject, thus ensuring that you have as accurate a field list as possible.

Refining Items Representing the Same Characteristic

Now look for items that represent the same characteristic and remove all but one. The idea here is that a given characteristic should appear only once in the list of characteristics. For example, assume that "Product #," "Product No.," and "Product Number" appear on your list of characteristics. It's evident that these items all represent the same characteristic, and you need only one of them on your list. Choose the one that conveys the intended meaning clearly, completely, and unambiguously and remove the remaining items from the list of characteristics. (In this case, the best choice is "Product Number" because it fulfills the previous criteria.)

Ensuring Items Represent Characteristics

Finally, make sure that each item on your list represents a characteristic . It's easy to place items accidentally on the list that represents subjects. You can test each item by asking yourself questions such as these:

Can this word be used to describe something?

Does this word represent a component, detail, or piece of something in particular?

Does this word represent a collection of things?

Does this word represent something that can be broken down into smaller pieces?

Depending on the item you're working with, some questions are easier to answer than others. When you find that an item represents a subject rather than a characteristic, remove it from the list of characteristics and add it to the list of subjects. Be sure to identify the new subject's characteristics and add them to the existing list of characteristics.

For example, say "Item" appears on your list of characteristics, and you're not quite sure whether it represents a characteristic or a subject. Use the questions above to help you make a determination.

Can "Item" be used to describe something?

Does "Item" represent a component, detail, or piece of something in particular?

You could make a case that "Item" helps to describe a sale inasmuch as it identifies what a customer purchased. On the other hand, you could also say that "Item" isn't a characteristic because it doesn't represent a singular aspect of a sale. "Date Sold," for example, represents a singular characteristic of a sale. Leaving the quandary surrounding these questions unresolved , you go on to the next question.

Does "Item" represent a collection of things?

You can answer this question easily by looking at the plural form of the word, which in this case is "Items." If "Items" can be referred to as a collection, it is a subject. It's beginning to become clear that "Item" does represent a collection of some sort , and you can make a final determination by asking yourself the last question:

Does "Items" represent something that can be broken down into smaller pieces?

You can answer this question by determining whether you can identify any characteristics for "Items." If you can, then "Items" definitely represents a subject and you should move it to the list of subjects. You also need to identify its characteristics and add them to your list of characteristics.

Continue with this procedure until you've reviewed and refined the entire list of characteristics to your satisfaction. When you are through, you have your first version of the preliminary field list . Now you'll add new items to it and refine it further during the next step.

Step Two: Determine Whether There Are New Characteristics in Any of Your Samples

This step involves an examination of all the samples you gathered throughout the analysis process. Your goal is to determine whether there are characteristics on the samples that need to be added to the preliminary field list.

Begin this step by highlighting every characteristic you find on each sample. Then, examine each characteristic and determine whether it's already on the preliminary field list; cross it out on the sample if it's already on the list. Next, study the remaining characteristics and determine whether any of them has the same meaning as an existing field; if it does, cross it out on the sample. (Use the same procedure you used in the first step to make this determination.) Finally, add any highlighted characteristics remaining on the samples to the preliminary field list.

For example, say you're working with the data-collection sample shown in Figure 6.14

Figure 6.14. An example of a data-collection sample.

Highlight each characteristic you find on the sample, as shown in Figure 6.15

Figure 6.15. A sample with highlighted characteristics

You're likely to find multiple occurrences of various characteristics in some of the samples. As you can see, both "Name" and "Phone No." appear twice on this particular sample. You can cross out the duplicates in this case because they have the same meaning as the original instances.

To continue with the example, say you reviewed the preliminary field list and found that every characteristic on the sample is already on the list with the exception of "Name" and "Phone No." Cross out the existing items on the sample to show that you have accounted for them. Before you add "Name" and "Phone No." to the preliminary field list, however, make sure that the names of these items properly describe their relationship to the subject represented within the sample. In this case, the two remaining items represent characteristics of a group of people known as "Contacts." Therefore, you rename these characteristics (using the subject as a prefix) as "Contact Name" and "Contact Phone Number," and then add them to the preliminary field list. Repeat this procedure for each sample you've gathered until you've gone through all the samples you've collected. When you're through, you have the second version of the preliminary field list.

A Side Note: Value Lists

As you examine the characteristics on a database, spreadsheet, or Web page sample, record on a sheet of paper the name of each characteristic that incorporates a value list (also known as an enumerated list ). This list specifies the acceptable range of values for a particular characteristic and often enforces a given business rule. (You'll learn about business rules in Chapter 11.) For example, say you work for a manufacturing company that uses four specific vendors to deliver its goods to customers across the nation. You could use a value list to ensure that a user selects one of those four vendors to ship a particular order. Figure 6.16 illustrates this example (note S HIP V IA ) and also shows two common types of value list.

Figure 6.16. A database screen with two value lists.

When you record the name of a characteristic that incorporates a value list, also record the values within the list. If the list contains a large number of values, write a brief description of the type of values in the list and (if possible) a minimum and maximum value; otherwise, write down each of the values. Figure 6.17 shows an example of the record you're creating.

Figure 6.17. Recording characteristics that incorporate value lists.

You can be discerning about the characteristics you choose to record. For example, it's unnecessary for you to record characteristics that accept simple or obvious sets of values, such as "yes/no," "true/false," or "active/inactive." Instead, you should record characteristics that accept distinct, specific sets of values.

Set this sheet (or sheets) aside after you've finished recording the appropriate characteristics. You'll refer to this sheet when you define field specifications for the fields in the database and again when you define business rules.

The Calculated-Field List

There's one final refinement you must make to the preliminary field list before you can consider it complete: You must remove every calculated field and place it on a separate list. This new list becomes your calculated-field list . Recall from Chapter 3 that a calculated field is one that stores the result of a string concatenation or mathematical expression as its value. You list calculated fields separately because you'll use them in a specific manner later in the design process.

You build the calculated-field list using existing fields from the preliminary field list. Examine the preliminary field list and determine whether there are fields that fit the description of a calculated field. Fields that have names containing words such as "amount," "total," "sum," "average," "minimum," "maximum," and "count" are likely candidates for the calculated-field list. Common names for calculated fields include "Subtotal," "Average Age," "Discount Amount," and "Customer Count." As you identify each calculated field, remove it from the preliminary field list and place it on the calculated-field list. When you've completed your examination of all of the fields in the preliminary field list, you'll have two completely new lists: a third version of the preliminary field list and a calculated-field list.

Reviewing Both Lists with Users and Management

Conduct brief interviews with users and management to review the items that appear on the preliminary field list and the calculated-field list. Your objective here is to determine whether there are fields that have been omitted from either list. You can continue with the next step in the design process when everyone is satisfied that the lists are complete; otherwise, identify the fields that are missing and add them to the appropriate list. Once the interviews are complete, you'll have a "final" version of each list.

Be sure you conduct these interviews because the participants ' feedback provides you with a means of verifying the fields on both lists. Let me remind you once again to avoid becoming too invested in the idea that these lists are absolutely complete and final. At this point you still may not have identified every field that needs to be included in the databaseinadvertently, you're almost sure to miss a few fieldsbut if you strive to make your lists as complete as you can, the inevitable additions or deletions will be quick and easy to make.

CASE STUDY

You've already defined the mission statement and mission objectives for Mike's new database. Now it's time to perform an analysis, conduct interviews, and compile a preliminary field list.

First, analyze Mike's current database. As you already know, he keeps most of his data on paper; the only exception is the product inventory he maintains in a spreadsheet program. Gather samples of the various papers Mike uses to collect data and a screen shot or printout of the spreadsheet he uses to maintain the product inventory. Assemble these samples together in a folder for later use. For example, Figure 6.18 shows a sample of the index cards Mike uses to collect customer information, along with a screen shot of his spreadsheet program.

Figure 6.18. A paper-based and a computer-generated sample from Mike's Bikes.

Next, identify the methods Mike uses to present information. He and his staff currently produce a variety of reports that present the information they need to conduct their daily affairs. They generate most of the reports using an old typewriter and the rest using a word-processing program on the computer. Gather samples of all the reports and place them in a folder for later use. Figure 6.19 shows a sample report that Mike creates with his typewriter.

Figure 6.19. A report sample from Mike's Bikes.

Now you're ready to interview Mike's staff. Here are some points to remember as you're conducting the interviews:

1. Identify the types of data staff members are using and how they use that data. Be sure to use the subject-identification technique and the characteristic-identification technique to help you analyze responses and formulate follow-up questions.

2. Review all the samples you gathered during the beginning of the analysis process. Determine how each sample is used, write an appropriate description, and attach the description to the sample.

3. Identify the staff's information requirements. Determine what information they're currently using, what additional information they need (remember to use the samples), and what kind of information they believe they'll need as the business evolves.

During the interview, one of the employees wonders whether she can add a new field to the supplier phone list report. How do you respond? You hand her the report and ask her to attach a note indicating the name of the new field and a brief explanation of why she believes it's necessary. When she's finished, return the sample to the report samples folder. Figure 6.20 shows the report sample with the attached note.

Figure 6.20. A report sample with attached note suggesting a new field.

You'll conduct the final interview with Mike. Keep the following points in mind as you speak with him:

1. Identify the reports he currently receives; you need to know what kind of information he uses to make business decisions. If he receives reports that are not represented in your group of report samples, obtain a sample of each report and add it to the group, updating the subject and characteristic lists as needed.

2. Review the group of report samples with him and determine whether he can identify subjects or characteristics that have been overlooked by his staff. Use the appropriate techniques to identify these items and then add them to the appropriate list.

3. Determine whether there is any additional information Mike needs to supplement the information he currently receives.

4. Determine what types of information Mike will need as the business evolves.

As you and Mike discuss his future information needs, he indicates that there is some new information he'd like to receive once the business really gets rolling: He'd like to see total bike sales by manufacturer. He believes this information would help him determine which bikes should be consistently well stocked. Such a report does not currently exist, so have Mike sketch it out on a sheet of paper. Next, identify the subjects and characteristics represented within the report and add them to the appropriate list. Then add the new report to your group of report samples. Figure 6.21 shows the sketch of Mike's new report.

Figure 6.21. The sketch of Mike's new report.

Your analysis is now complete. You've interviewed Mike and his staff, you've gathered all the relevant samples, and you've created a list of subjects and a list of characteristics. A partial list of subjects and characteristics is shown in Figure 6.22. All you need to do now is to create your preliminary field list.

Figure 6.22. Partial lists of subjects and characteristics for Mike's Bikes.

As you already know, you need to refine the list of characteristics before it can become the first version of the preliminary field list. Remove all duplicate characteristics, delete items that represent the same characteristic, and refine those items that have generic names. (Remember the problem with the characteristic called "Name"? If you find such characteristics, now is the time to resolve them.) Next, review all your samples and determine whether they contain characteristics that do not currently appear on the preliminary field list. Add to the list any new characteristics that you find. When you complete these tasks , you have the first version of your preliminary field list.

Now you remove all the calculated fields from the preliminary field list and place them on their own list; this becomes your new calculated-field list. Figure 6.23 shows a small portion of your final preliminary field list and calculated-field list.

Figure 6.23. A partial preliminary field list and a calculated-field list.

Note

You may have noticed that each list includes a date in the title. It's a good idea to date your lists so that you can maintain a clear history of their development.

---

Top