When Null just isn't right

An alternative to Null

When entering a new record or filling out a form, you don't always have all the information at hand. For instance, if you're entering patient information in a hospital's emergency room, you might know the patient's name, but not his or her address, social security number, next of kin, and so on. At this point, these values are null-they are unknown or unavailable. In this case, null values are appropriate and necessary. Later, you can complete the record once you've had a chance to collect all the necessary information.

Not all missing information is null, meaning unknown or unavailable. Sometimes, there simply isn't an appropriate value. For example, not everyone has a fax or a cell phone number. Once you know there's no value, you might want to supply a visual clue, such as "N/A." This way, you can distinguish between truly null values and those that simply don't apply. Doing so isn't just a convenient way to alert users but can also impact criteria. For instance, a query for missing phone numbers shouldn't return records where the phone number is already known not to exist.

How you enter N/A or some equivalent, is up to you. Just don't leave it up to data entry personnel or you'll end up with inconsistent entries: na, NA, N/A, n/a, not applicable, and so on. You lose control when you let users decide.