espite having a good understanding of the term null
, most developers still fail to adequately accommodate null values in their databases. Accommodating null values appropriately is an important part of protecting the validity of your data. At the very least, mismanaging nulls leads to erroneous data. If you don't catch the bad data, it could lead to unsound business decisions.
The concept of null is often misunderstood. Some experts describe null as nothing or an empty value. In other words, no value has been entered for that entity. Others describe a null value as an unknown value. The value may or may not exist, but it is presently unknown. Technically, the ANSI standard states that null is not a data value, but an indicator that data is missing.
Now, let's apply the concept of null to actual data. For example, when reviewing a new customer record, the user might see a main phone number but not a fax or cell phone number. At that point, the user doesn't know whether the new customer actually doesn't have a fax or cell phone or simply failed to provide that data. If dealt with properly, these nulls would have shared important information, but instead the data is not known.
Dealing with Nulls
There's no right or wrong way to deal with nulls; the keys are awareness and consistency. The place to start is at the table level. For instance, properly normalized tables usually eliminate the phone number type of null because they won't even create a record for a fax or cell phone number until a user enters that information.
Not all nullables can be normalized away though. When that's the case, eliminate null values at the column level by requiring a value. Then, you have to decide whether to set a default value or allow a zero-length string. Invariably, however, you will face a few nullable columns. Either a business rule will force the issue or you'll encounter them in a legacy application.