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

fields as well; they will appear in the table as I NST S TREET A DDRESS , I NST C ITY , I NST S TATE ,
and I NST Z IPCODE . Figure 7.15 shows the newly revised INSTRUCTORS table.
Figure 7.15. Resolving the multipart fields in the INSTRUCTORS table
Some multipart fields are hard to recognize. Take a look at the INSTRUMENTS table in
Figure 7.16 . At first glance, the table doesn't seem to contain multipart fields. When you
examinethedatainthetablemoreclosely,however,you'llseethatI NSTRUMENT IDisactu-
allyamultipartfield.Thisfield'svaluerepresentstwodistinctitems:thecategorytowhich
the instrument belongs—AMP (amplifier), GUIT (guitar), MFX (multi-effects unit), SFX
(single-effect unit)—and the instrument's identification number. Clearly, you should de-
constructI NSTRUMENT IDintotwosmallerfieldsinaccordancewiththethirdelementofan
idealfield.Imaginehowdifficultitwouldbeforyoutoupdatethefield'svalueiftheMFX
categorychangedtoMFUifyoudon'tdothis.Youwouldhavetowriteprogrammingcode
to parse the value, test for the existence of MFX, and then replace it with MFU if it existed
withintheparsedvalue.It'snotsomuchthatyou can't dothis,butyouwoulddefinitelybe
working harder than necessary, and you shouldn't have to go through this at all if you have
a properly designed database.