Terminology - Database Design for Mere Mortals - page 34

string value in a field called M IDDLE I NITIAL may represent the fact that a particular
employee does not have a middle initial in his name.
Note
Due to space restrictions, I cannot always show all of the fields for a given sample
table. I will, however, show the fields that are most relevant to the discussion at
hand and use <<other fields>> to represent fields that are unessential to the ex-
ample. You'll see this convention in many examples throughout the remainder of
the topic.
The Value of Nulls
Anullisquiteusefulwhenyouuseitforitsstated purpose,andtheCLIENTS table in Fig-
ure 3.3 clearly illustrates this. Each null in the C LIENT C OUNTY field represents a missing
or unknown county name for the record in which it appears. In order for you to use nulls
correctly, you must first understand why they occur at all.
Missing values are commonly the result of human error. For example, consider the record
for Shannon Black. If you're entering the data for Ms. Black and you fail to ask her for the
name of the county she lives in, that data is considered missing and is represented in the
record as a null. Once you recognize the error, however, you can correct it by calling Ms.
Black and asking her for the county name.
Unknown values appear in a table for a variety of reasons. One reason may be that a
specific value you need for a field is as yet undefined. For instance, you could have a
CATEGORIES table in a School Scheduling database that doesn't currently contain a cat-
egory for a new set of classes that you want to offer beginning in the fall session. Another
reason a table might contain unknown values is that they are truly unknown. Refer to the
CLIENTS table in Figure 3.3 once again and consider the record for Marvin Russo. Say
that you're entering the data for Mr. Russo and you ask him for the name of the county
he lives in. If he doesn't know the county name and you don't happen to know the county
that includes the city in which he lives, then the value for the county field in his record is
truly unknown and is represented within the record as a null. Obviously, you can correct
the problem once either of you determines the correct county name.
A field value may also be null if none of its values applies to a particular record. Assume
foramomentthatyou'reworkingwithanEMPLOYEEStablethatcontainsaS ALARY field
and an H OURLY R ATE field. The value for one of these two columns is always going to be
null because an employee cannot be paid both a fixed salary and an hourly rate.