Database Reference
In-Depth Information
Chapter 12
,
“
Views
,
” that you can work with fields from the INSTRUMENTS table
and
the MANUFACTURERS table at the same time by combining them within a
view
(virtual
table). You can then use this view as the basis for compiling any reports you require.
Duplicate fields that serve to indicate multiple occurrences of the same type of value are
unnecessary as well. For example, take a look at the version of the STUDENTS table
presented in
Figure 7.24
.
Figure 7.24. A simple example of a table containing unnecessary duplicate fields
I
NSTRUMENT
1, I
NSTRUMENT
2, and I
NSTRUMENT
3 are duplicate fields that represent mul-
tipleoccurrencesofthe
same type
ofvalue.Theirpurposeinthetableistoenablethemusic
department to keep track of the instruments checked out by a given student. Aside from
the difficulties these fields pose in retrieving information about a particular instrument, the
fieldsalsolimitthenumberofinstrumentsastudentcancheckout.Whathappensifseveral
students want to check out more than three instruments?
Does this type of field structure look strangely familiar? It should! It's similar to the one
backin
Figure7.18
.Asyou'veprobablyalreadyguessed,it'snothingmorethanaflattened
multivalued field. Mind you, the person who created this table probably didn't have a mul-
tivalued field in mind (and neither do most folks who create fields such as these), but that
is what it truly is.
You already know how to deal with these unnecessary duplicate fields because you know
how to resolve multivalued fields. You can easily fix the STUDENTS table by first visu-
alizing the I
NSTRUMENT
1, I
NSTRUMENT
2, and I
NSTRUMENT
3 fields as a singular multival-
ued field, and then resolving it as you would any multivalued field.
Figure 7.25
illustrates
this process. The shaded version of the STUDENTS table shows how you visualize the in-
strument fields as a singular multivalued field. You then resolve the multivalued field by
