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

Duplicate fields that serve to provide reference information (also known as reference
fields ) are unnecessary and easy to resolve—you just remove them from the table. Unfor-
tunately, many people believe that a table must contain every field that will appear in the
reports they generate from it, so they introduce into the table various duplicate fields they
deem are necessary. They assume that the table will then be able to provide all the requis-
ite information for their reports. But they are mistaken, and their action is both unwise and
undesirable. Tables containing reference fields exhibit poor design and will have a number
of problems, many of which will become increasingly clear as the database design process
unfolds. Reference fields force the user or database application program to ensure that the
values in all occurrences of the field are mutually consistent, a process that carries a high
risk of error. Figure 7.23 shows an example of a table containing reference fields.
Figure 7.23. Example of a table containing reference fields
TheM AN P HONE andW EB S ITE fieldsintheINSTRUMENTStablearereferencefieldsand,
bydefinition, are actually unnecessary duplicate fields. Youcertainly don't need to include
them in this table because they're already part of the MANUFACTURERS table structure;
therefore, you can remove them from the INSTRUMENTS table in order to resolve the
unnecessary duplication problem. (M ANUFACTURER is not a reference field because it cur-
rentlyrelatestheINSTRUMENTStabletotheMANUFACTURERStable.)You'lllearnin