Establishing Table Structures - Database Design for Mere Mortals - page 165

Figure 7.14. Testing a table with sample data
Note
As I mentioned in Chapter 3 , I show only those fields that are most relevant to the
discussionathandanduse <<other fields>> torepresentfieldsthatareinessential
to the example.
Now you can easily identify which fields are going to be troublesome unless they are
resolved. As you can see, I NST N AME and I NST A DDRESS are both multipart fields, and
C ATEGORIES T AUGHT is a multivalued field. You must resolve these fields before you can
refine the table structure.
Resolving Multipart Fields
Working with a multipart field is difficult because its value contains two or more distinct
items. It's hard to retrieve information from a multipart field, and it's hard to sort or group
the records in the table by the field's value. The I NST A DDRESS field in Figure 7.14 illus-
trates these difficulties; you'd certainly have problems retrieving information for the city
of Seattle or sorting information by zip code.
You resolve a multipart field by identifying the distinct items within the field's value and
treating each item as an individual field. Accomplish this task by asking yourself a simple
question: “What specific items does this field's value represent?” Once you've answered
thequestionandidentifiedtheitems(asbestyoucan),transformeachitemintoanewfield.
In Figure 7.14 , the value of the field I NST N AME represents two items: the first name and
the last name of an instructor. You resolve this field by creating a new I NST F IRST N AME
field and a new I NST L AST N AME field. The value of I NST A DDRESS represents four items:
the street address, city, state, and zip code of an instructor. You transform these items into