Database Reference
In-Depth Information
A Word about Redundant Data and Duplicate Fields
You've seen the term
redundant data
used quite often in this chapter. Redundant data was
characterized as being unacceptable in many cases, but appropriate in others. In order for
you to better understand how to determine when redundant data is acceptable, a definition
of the term is in order.
Redundant data
is a value that is repeated in a field as a result of the field's participation
in relating two tables or as a result of some field or table anomaly. In the first instance, the
redundant data is appropriate; by definition, a field used to relate one table to another will
contain redundant data. (You'll learn more about this in
Chapter 10
.
)Redundant data is en-
tirely unacceptable in the second instance, however, because it poses problems with data
consistency and data integrity; therefore, you should always strive to keep redundant data
to an absolute minimum.
A
duplicate field
is a field that appears in two or more tables for any of these reasons.
• It is used to relate a set of tables together.
• It indicates multiple occurrences of a particular type of value.
• There is a perceived need for supplemental information.
The only instance in which a duplicate field is necessary is when it serves to establish a re-
lationship between two tables; it provides the sole means of associating records in the first
table with records in the second table. Duplicate fields are unnecessary in all other cases,
and you should avoid them because they introduce needless, redundant data.
As you refine each table structure, you'll assess whether to retain a given duplicate field in
the table. If the reason for its existence in the table is valid, then you'll keep it; otherwise,
you'll remove it. You'll learn how to deal effectively with both redundant data and unne-
cessary duplicate fields in the following sections.
Using an Ideal Table to Refine Table Structures
Despite your efforts to refine the fields in a table, the table structure itself may contain an-
omaliesthatcanproduceunnecessaryredundantdataandmakeitdifficulttoworkwiththe
data in the table. You can identify a potentially problematic table structure by determining
whetheritcomplieswiththeElementsoftheIdealTable.Theseelementsconstituteasetof
guidelines you can use to create sound table structures and to spot poorly designed tables
easily.
Elements of the Ideal Table
•
It represents a single subject, which can be an object or event.
Yes, I know, I've
said this a number of times already. The fact of the matter is that I can't overem-
