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

some fields is to determine whether they comply with the Elements of the Ideal Field.
These elements constitute a set of guidelines you can use to create sound field structures
and to spot poorly designed fields easily.
Elements of the Ideal Field
• It represents a distinct characteristic of the subject of the table. As you know, a
table represents a specific subject, which can be an object or event. The ideal field
represents a distinct characteristic of that object or event.
• It contains only a single value. A field that can potentially store two or more oc-
currences of the same value is known as a multivalued field. A multivalued field
causes data redundancy problems (quite obviously) and is difficult to use when
you try to edit, delete, or sort the data within it. The ideal field is free of these
problems because it contains only a single value.
• It cannot be deconstructed into smaller components. A field that can potentially
store two or more distinct items within a value is known as a multipart (or com-
posite) field. Like the multivalued field, this type of field causes problems when
you try to edit, delete, or sort the data within it. These problems don't occur with
an ideal field because it represents a single, distinct characteristic of the subject of
the table to which it belongs. (You'll learn more about multivalued and multipart
fields in just a moment.)
• It does not contain a calculated or concatenated value. The values of the fields in
a table should be mutually independent; a particular field should not have to de-
pend on the values of other fields for its own value. A calculated field, however,
does depend on the values of other fields for its own value, and therein lies the
problem. The calculated field's value is not updated when the value of any field
participating in the calculation changes. It then becomes the responsibility (and an
undesirable burden) of the user or the database application program to update the
calculated field when this type of change takes place. This is precisely why you
deal with calculated fields separately.
• It is unique within the entire database structure. The only duplicate fields that ap-
pear in a properly designed database are those that establish relationships between
tables. If duplicate fields other than these exist in a table, it is very likely that the
table will accumulate unnecessary redundant data and that the data within the du-
plicate fields will inevitably become inconsistent.