Database Reference
In-Depth Information
It's important to note that there is a very slim difference between “does not apply” and “is
not applicable.” In the previous example, the value of one of the two fields literally does
not apply. Now assume you're working with a PATIENTS table that contains a field called
H
AIR
C
OLOR
and you're currently updating a record for an existing male patient. If that
patient recently became bald, then the value for that field is definitely “not applicable.”
Although you could just use a null to represent a value that is not applicable, I always re-
commend that you use a true value such as “N/A” or “Not Applicable.” This will make the
information clearer in the long run.
As you can see, whether you allow nulls in a table depends on the manner in which you're
usingthedata.Nowthatyou'veseenthepositivesideofusingnulls,let'stakealookatthe
negative implication of using them.
The Problem with Nulls
The major disadvantage of nulls is that they have an adverse effect on mathematical op-
erations. An operation involving a null evaluates to null. This is logically reasonable—if
a number is unknown then the result of the operation is necessarily unknown. Note how a
null alters the outcome of the operation in the following example:
(25 × 3) + 4 = 79
(Null × 3) + 4 = Null
(25 × Null) + 4 = Null
(25 × 3) + Null = Null
The PRODUCTS table in
Figure 3.4
helps to illustrate the effects nulls have on mathem-
atical expressions that incorporate fields from a table. In this case, the value for the T
OTAL
V
ALUE
field is derived from the mathematical expression “[SRP] × [Q
TY
O
N
H
AND
].” As
you inspect the records in this table, note that the value for the T
OTAL
V
ALUE
field is miss-
ing where the Q
TY
O
N
H
AND
value is null, resulting in a null value for the T
OTAL
V
ALUE
field as well. This leads to a serious
undetected
error that occurs when all the values in the
T
OTAL
V
ALUE
field are added together: an inaccurate total. This error is “undetected” be-
cause an RDBMS program will not inherently alert youofthe error.The only way to avoid
this problem is to ensure that the values for the Q
TY
O
N
H
AND
field cannot be null.
