Null or not to null

[doco]

In response to: http://www.access-programmers.co.uk/forums/showthread.php?t=131593

I personally believe 'NULL' should be banned as a possibility entirely.
It has been pounded into my head that 'NULL' is not the same as 'EMPTY' or 'ZERO'; and that it literally means 'NOT KNOWN'. Now consider a contact database that has a field for lets say Apt# or Street Direction such as N or S or ?? as an example. There are many, many instances where these fields would be left blank or empy: NULL | NOT KNOWN. When in fact the situation is known: the object has no such attribute; and in query or some other form of program logic the venerable NZ(subject, response) function may be used which would replace the NULL with a value. Or program logic and or error trapping would be used to handle such occasions. Which is an absurd waste of time and talent IMHO.

Therefore, if there is no applicable attribute to enter (something known) why not say so with 'N/A'? Else what we are saying by using 'NULL' is that we are building a dataset containing data that cannot be known. Kind of like saying

This statement is FALSE

 

[Banana]

This posts was originally from this thread which was very long and detailed discussion about nulls.

Read it up.

 

[CraigDolphin]

In general I agree with you. But, to play devils advocate ....

Therefore, if there is no applicable attribute to enter (something known) why not say so with 'N/A'?

If I want to create a mailing list to print on address labels I populate a text box with an address string concatenated from the various fields. An 'empty' field is easier to work with than one that contains N/A since I don't first need to check whether the content = 'N/A'.

ie:
strX = fieldA & (", " + fieldB) & (", " + fieldC) ...etc

is easier for me than using nested conditional-if statements or somesuch...

 

[doco]

is easier for me than using nested conditional-if statements or somesuch...

Yes of course. One of those damned if you do and don't. I suppose it then becomes a business rule issue.

It still is frustrating seeing datasets with blank fields. A blank field is a little more intuitive I suppose, for the example I gave, than say a field representing some other qualitative datum: did it have a quality or not? Was it an oversight input or an allowed NULL?

 

[CraigDolphin]

I agree and the whole discussion about nulls is making me think really hard about the issue. Doc_Man made the point early on that using nulls in field is a mark of a lazy developer in his estimation. I now understand a lot more what he meant by it and he's right.

In the example above, my approach is 'easiest' not necessarily the 'best'. Address fields might be one case where I disobey the 'best' thing for pragmatic reasons.

However, in other cases I will be much more careful in future. For example, one case where I had previously noted the presence or absence of a clipped adipose fin using a yes/no/null system (present/absent/not known[tail of fish missing/not recorded etc]) the use of null is clearly inapproriate *sigh*.

I should have gone with something like present, absent, anatomy missing, not recorded, or some such scheme that eliminates the use of nulls.

But that's the great thing about this forum: you have access to the wisdom of some very intelligent and vastly more experienced folks to help steer you straight if you're willing to learn.

 

[The_Doc_Man]

I suppose it then becomes a business rule issue.

Use or non-use of NULL is NEVER EVER IN A GAZILLION YEARS a business rule issue. It might, however, be a corporate style issue.

Your business rules, in the sense that we usually mean them in this forum, would tell you data flow, data origin, data disposition, data interaction. They were created by your accounts and finance gangss and sales gangs and any other contributers who shaped your business flow. And the odds are that the lot of them wouldn't know a NULL if you hit them over the head with one. (But then, since it is NOTHING, they might not notice it either...)

I have never yet seen a business rule that dictates use of NULL. Now, here is where the nit-pick starts. Your model of the business, as implemented in Access, COULD use a NULL in highly limited cases. And the thread between me and Banana explored exactly what those cases would mean. But it still would not be a business rule. It would be a modeling decision (implying more than a little bit of discretion in the choice).

NULL, properly used, has a specific and useful meaning. Where all too many folks go awry is to stretch that meaning into something it is not. Follow that link that Banana gave you to see what I mean. I'm not going to repeat THAT monster of thread here.

 

[doco]

Follow that link that Banana gave you to see what I mean.

I'm the one who originally pointed to your article and inspired me to vent.

Your business rules, in the sense that we usually mean them in this forum, would tell you data flow, data origin, data disposition, data interaction. They were created by your accounts and finance gangss and sales gangs and any other contributers who shaped your business flow. And the odds are that the lot of them wouldn't know a NULL if you hit them over the head with one. (But then, since it is NOTHING, they might not notice it either...)

Or in my case Oregon Revised Statute. The only thing worse than lawyers doing math is taking direction from legislators on any subject...

At any rate, the use of null in any model I am involved in is kept to a near non-existent minimum. Again IMHO, and taking the lead from the article, creating datasets whose expressed purpose is to collect nothing is a bit like explaining:

This statement is FALSE

As was pointed out to me concerning the addressing issue, there are times when an empty field is useful; and in that case, intuitive; and should be. However, in my world, those instances are very rare indeed. So, to call that a 'business rule' rather than a modeling protocol is I suppose a fopah

I still believe my point valid and I think that of the article: NULL should be avoided.

 

[Rabbie]

NULLS are unnecessary in most Text fields since a ZLS can ussually be used. However there can be a need for NULLS in a number field as there is no way to differentiate between a known value of zero and an empty field.