Best Text Field Defaults: Null or Zero Length String

[Max Moor]

Hi All,

I'm getting ready to start a new application. The first time I did a
database app, I didn't put default value in for my tables' text (or memo)
fields. The last time, except for a few cases where I needed some specific
default, I set them all to zero-length strings (""), with zero-length strings
allowed, of course.

Now I'm getting ready to lay out my tables for this new app, and it
occurs to me to ask the experts before I do this again. Are there issues I
haven't noted that make doing this a bad idea? Is there a "best practice"
for setting field defaults in tables?

Thanks for the help.

Regands,
Max

 

[Douglas J. Steele]

My preference is to allow Null fields where they make sense. I'd much rather
have Null for a person's middle initial if I don't know their middle initial
than have a zero-length string.

 

[Duane Hookom]

I have never allowed zero length strings. IMHO if there is no value to enter
into a field it should be null.

 

[Allen Browne]

Use nulls, never zero-length strings (ZLS).

In database theory, Null means things like:
- unknown
- not applicable
- undefined
- not yet assigned
- to be announced

ZLS means the value is known not to exist. For example, in a client table,
someone might put a field called MiddleName. If you know that Jenny Jones
has no middle name, then you could represent that data as a ZLS in the
field, as distinct from Null which would mean we don't know if the person
has a middle name.

It would matter if you asked a question such as, "What percentage of our
users have a MiddleName of 'Sam'?" Nulls are ignored in statistics
(counting, averaging, etc), whereas ZLSs are not ignored. As an example, say
you had 10 records: 2 x Null, 1 x ZLSs, 2 x 'Sam', and 5 other names. The
percentage of Sams would be 2/8 = 25%, since the 2 nulls are not counted,
but the ZLS is.

In practice, that distinction is rarely of any practical use, and far too
subtle for users to understand. Even developers get confused by the
difference, so all it does is increase the chance that you get wrong answers
(because the question has to be even more precise than the visible interface
can see.)

There is also a performance difference: testing for Null is faster than
testing <> "" in JET.

So, my recommendation would be to disable ZLS on all your text fields. I
actually run this FixZLS() code on my databases after the schema is built:
http://allenbrowne.com/bug-09.html

There is a case where I do use ZLS, and that's where a field is required and
part of a unique index. For example, if you are hiring out commercial
addresses, you might create a unique index on UnitNumber + StreetNumber +
StreetName + City (since you must not have 2 records for the same unit.) But
sometimes UnitNumber doesn't apply. A ZLS in this field blocks duplicates,
but allows it to be part of a unique index (even primary key.) Default Value
is "".

Some users are tempted to use ZLS so they don't have to learn to handle
Null. Not a good idea:
http://allenbrowne.com/casu-11.html

 

[Max Moor]

My preference is to allow Null fields where they make sense. I'd much
rather have Null for a person's middle initial if I don't know their
middle initial than have a zero-length string.

Thanks, Duane, Allen, and Doug. I couldn't have asked for a more credible
concensus. Now I've got to go do some rethinking.

Regards,
Max