Setting defaults to avoid Null values

[Bobk]

Is there a standard protocal for setting defaults in a table to avoid Null
values from occurring? I'm thinking that all numeric fields should have a
default of zero (0), text fields should have a default of one space (" ") and
any status fields such as, in the case of a Purchase Order, "Open". I have
had particular difficulty in selecting records based on looking at a status
field, for example, and discovering that the record does not exist as a
result of the presence of a "Null" value in the field. I now understand the
Null. My question is - What do you experienced programmers do with table
design to minimize the downstream programming issues?

 

[Rick Brandt]

Is there a standard protocal for setting defaults in a table to avoid Null
values from occurring? I'm thinking that all numeric fields should have a
default of zero (0), text fields should have a default of one space (" ") and
any status fields such as, in the case of a Purchase Order, "Open". I have
had particular difficulty in selecting records based on looking at a status
field, for example, and discovering that the record does not exist as a
result of the presence of a "Null" value in the field. I now understand the
Null. My question is - What do you experienced programmers do with table
design to minimize the downstream programming issues?

Learn to deal with Nulls. What problem specifically did you have with
"selecting records based on looking at a status field"? Nulls are generally
only an issue in criteria when testing for non-equivalence. For example..

SELECT * FROM TableName
WHERE Status <> "Some Text"

....will not include those records where the Status field is null. If you want
those included you just use...

WHERE Status <> "Some Text" Or Status Is Null

Criteria based on equivalence does not have this problem so you really only need
to be "Null aware" when using not equals, less than, not like, etc., and in all
those cases adding "Or FieldName Is Null" is all you need to do to handle it.

 

[Bobk]

Secifically, I had a problem in selecting records in a Query where I was
selecting a range of Purchase Orders within a date range which worked ok.
When I added the status parameter of "open" or "closed" I didn't get the
Null status items when selecting all or I would get "open" plus Nulls or
"closed" plus Nulls. I have corrected the problem by making sure the status
field contains a valid status rather than Null. If I had had a little more
experience with the Null effect I would have added a default value when
initially setting up the table. It just occrred to me that someone must have
thought about this and has some general guidelines for table design that
would make things a little easier and more bullet proof.

 

[Rick Brandt]

Secifically, I had a problem in selecting records in a Query where I was
selecting a range of Purchase Orders within a date range which worked ok.
When I added the status parameter of "open" or "closed" I didn't get the
Null status items when selecting all or I would get "open" plus Nulls or
"closed" plus Nulls. I have corrected the problem by making sure the status
field contains a valid status rather than Null. If I had had a little more
experience with the Null effect I would have added a default value when
initially setting up the table. It just occrred to me that someone must have
thought about this and has some general guidelines for table design that
would make things a little easier and more bullet proof.

Well in most cases like that rather than setting a default to avoid a Null I
would make it a required field in the table design so the user would be forced
to make an entry before the record could be saved.

 

[Bobk]

You are right. For the most part I do that. Anything I can enter
automatically I use default, like timestamping data entry. In my case where a
new Purchase Order is being entered, I should have defaulted the status field
to "open" which is the status of a newly entered Purchase Order. The status
is either "open" or "closed". If I had been a little more astute I would done
that rather than wrestle with Nulls.

Thanks for the input.

 

[Jeff Boyce]

To a user, a field with a space (" ") looks "empty" (sorta like a Null,
don't you thing?!).

I'd put myself in the "with experience" camp, rather than the "experienced"
group ... and I allow Nulls because of my experience with statistics. A
Null indicates nothing's been done, no choice made/entered. A zero-length
string does the same for text fields, but all three (" ", zls, Null) appear
the same to a user, and can cause confusion. After all, how do you know
what the user really meant if there's no data entered? Was their response
"none of these", "none", or have they just missed the item?

I'd rather force a choice that guess what was intended.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/