AllowZeroLength or Not

[Shep]

I've been at this for quite some time now; creating databases and applications for a small company. I have gotten along ok by studying various books, with the gracious help of many exceptionally knowledgeable people in this forum, and by making heavy use of the archives here.

I have moved on, however, having found what many of us would likely consider a dream gig. I am now part of a team which will develop an application and associated database which we will make available (sell) to other companies. Pretty scary, considering I have no formal training, but I intend to make the best of it.

I of course want a solid foundation, and have a basic design question:

Briefly, which things should one consider when deciding whether to allow zero length on a text field?

And a related question:

I have in the past set AllowZeroLength to No, primarily because my installation(s) of Access have always defaulted to No.

In my current installation (2003), it defaults to Yes.

How can the default be changed?

I found the same question posted in the archive, but nobody replied to the post.

Thanks, and may I just extend at this point my deep gratitude to those who have been so willing to share their extensive knowledge and invaluable experience with everyone.

 

[RoyVidar]

I don't think you can change the default, I'm afraid. But I think several of us are using some smallish routines to "defeat" defaults and other peculiarities of MS Access from time to time.

Here's an issue tester from Allen Browne
http://allenbrowne.com/AppIssueChecker.html

Here's again, from Allen Browne, two snippets curing this, and the SubDataSheet thingie
http://allenbrowne.com/bug-09.html

 

[jkl0]

If you set AllowZeroLength to Yes, then
if you are assigning a value to the text field then you can use:
yourtxtfield = ""

If you set AllowZeroLength to No, Then you can't do the above.
You would have to make the text field Null.

 

[The_Doc_Man]

Also, if you are setting an index on such a field, Using AllowZeroLength = YES means you must set up the index to have Duplicates Allowed.

 

[Shep]

Thanks for the replies. I think I wasn't as clear as I could have been. I was wondering in which situations, or for what considerations, one might allow zero lenth strings in a text field.

Personally, I don't see the point in ever allowing it. Better put, I don't see the point in allowing it for some text fields, and some not. If all 'empty' text fields are Null, or if all 'empty' text fields are zero length strings, then one can be consistent with querying whether a field Is or Is Not Null, or whether the Len(strField) equals whatever.

Then again, I constantly find there are some basic, fundamental issues I am lacking in. Maybe there are times when allowing zero length is crucial.

 

[RoyVidar]

I thought I was clear by saying something along the lines of defeating defaults and peculiarities.

OK here's my view or take on it - I've never seen any point in differentiating between Null and ZLS.

I think Null is what has historically been used to denote the absence of value, I think that suffices. I think that defaulting this darned property to yes (starting with the 2000 version) constitutes a bug. If that means I too have missed out on something fundamental with regards to relational theory, then so be it

 

[Shep]

Thanks Roy, I admit I didn't catch your drift. I assumed the ability to set this property was a measured decision on Microsoft's part. If it's considered a peculiarity by most, then I can only say that I agree. I cannot remember ever having encountered a situation where a light bulb popped on and I ran to snap open a database and set AllowZeroLength in a cold sweat.

 

[The_Doc_Man]

You allow zero-length strings to save space in a field that will usually - or at least often - be "sparse". For instance, imagine a quality control tracking application. If you inspect something, have a check-mark for "passed inspection" and a text box for "discrepancies" - you might wish the discrepancies field to be zero if there were no discrepancies, rather than waste your time by typing in "none".

 

[RoyVidar]

I'm sorry, I don't understand the sample. If you're saying that this is a case where allowing ZLS is to be preferred vs allowing Nulls, I don't get it. Could you please explain?

 

[tehNellie]

Again, this is my take on whether or not to allow zls.

If you think of NULL as equivalent to "I don't know", is that a valid response to store within that column? A middle initial in a name for example, might be "A", might be "I don't know"/NULL or might be ZLS, all are potentially valid values for that column depending on how your application works.

One consideration to bear in mind is whether you want to perform any comparisons on that data, anything compared or calculated against NULL will always return NULL as a value, whereas a comparison/calculation against "nothing"/zls can more accurately be made. In your example of using Len(strColumn), if you have a mix of strings, zls and NULL then your code, if not written to take it into account, will crash whenever it encounters NULL but will properly report on a zls.

Both "nothing" and "I don't know" are valid imo, if it's data I'm likely to supply later but might not have to hand right now, then I'll allow NULL to be stored in that column, otherwise I default it to a ZLS. I've just had too many problems with NULL values in columns where I'm expecting data to use NULL as default where it isn't appropriate for that column.

 

[The_Doc_Man]

OK, let's try this approach.

Sometimes you are not ready to finish the popultion of a record because you are waiting for results, but you want to capture what you have done so far. You would allow a ZLS or NULL as the contents of a field you haven't filled in yet - but intend to.

One important factor to consider, as Nellie pointed out, is whether the answer "I don't know" is valid. If it is, then a ZLS is a simple way to define that result. I tend to avoid allowing NULL for reasons already defined in earlier posts. When you want to avoid NULL but allow ZLS, you put a default value in the record definition and you are good to go.

My comment about sizing is because (as I understand it) a text string allocated with a fixed size, oh, say 100 bytes, doesn't really take up 100 bytes before it is first loaded. It takes up a string descriptor of some sort that points elsewhere. The descriptor is a place-holder that is the same size whether we are talking ZLS or 100 characters. Data in the place holder tells us the size and location of the "real" string. But Access transparently locates the string for us so our programs don't have to.

I want to say that somewhere I have heard that such a descriptor is always 8 bytes long, but I can't recall where I heard that and it might be incorrect. In any case, it is this method of storing strings that makes a ZLS meaningful. If it is a ZLS, there IS no "real" string because one was never stored. But the place-holder was at least initialized. (cf. NULL where the place-holder was NOT initialized.)

 

[RoyVidar]

The_Doc_Man,
I'm pursuing the issue, because anywhere I've read about this topic (ZLS vs Null), the recommendation has been the opposite of yours, namely, use Null to denote absence of value, and disallow ZLS (together with other buggy/problem properties, "Auto Corrupt", Subdatasheet to name a few).

As seen in the Allen Browne link, he lists this (defaulting to allow ZLS) under the category "Other bugs", and states "In Access 2000 and later, the property defaults to Yes, and you must remember to turn it off every time you add a field to a table." (bold part is in the original article) and "There is no justification for having this property on by default. There is no justification for the inconsistency with previous versions.".

But, he also says "The savvy developer uses engine-level validation wherever possible, and permits a ZLS only in rare and specific circumstances." - and I was kind of hoping that you would give one of these rare and specific circumstances. What tehNellie describes, is using both ZLS and Null to denote different reasons for absence of value, which, in my view, is one such circumstance (I'll follow up with a sample at the bottom of the reply), but I asked you why you think ZLS should be used over Null in your sample (quality control tracking), to which I still can't understand the answer.

What you have given, seems to me, to be circumstances where ZLS and Null are interchangeable. When that is the case, I'm with Allen Browne et al.

But, you've given us something to test and verify - based on your assumptions, two identical databases where one allows ZLS, the other allows Null should be of different sizes, shouldn't they? Based on the descriptors, 8 bytes, initializations and whatnots?

In a db with table Table1, with id Autonumber, tst text 35 allowing ZLS, i put 163 840 rows having only the Autonumber and ZLS. After compact and repair, the size was 4 876KB.

In a db with table Table1, with id Autonumber, tst text 35 disallowing ZLS, i put 163 840 rows having only the Autonumber and Null. After compact & repair, the size was 4 876KB.

To really test, I updated all the text fields with the text "test", which made both dbs 5 708 after compact & repair, then set it back again to ZLS and Null respectively, which set both databases back to 4 876 KB after compact & repair.

Then I suddenly thought that since I had to update each text field, also those with Nulls, that perhaps this would "initialize" those fields. So, I created two new database, this time with two text fields. Then populated the same amount of records, this time being careful not to touch the "Null" field (appended only to the other text field). But again, after compact & repair, both db's showed identical sizes (5 868KB).

I e - no difference in size.

So again - I don't understand why you think one should choose ZLS over Null to denote absence of value in your sample (quality control tracking), or anywhere, for that sake. In fact, I don't seem to neither understand where you've given your reasons for why ZLS should be preferred over Null in you sample, nor what your actual reason are. If there is something I've completely missed, I would like to know, else I'll continue to side with Allen Browne et al

The only time I think it could be handy, is if you need to differentiate between two different types of absence of value. For instance, as tehNellie refers to, middle initial, or perhaps middle name. For some people, the middle name is unknown, and some people simply don't have such.

One could use Null to denote that we don't know this persons middle name, and ZLS to denote that this person does not have a middle name.

In the following sample illustrating this, we don't know Johns middle name, but we know Jane doesn't have one.

INSERT INTO mytest (fname, mname, lname) VALUES ('John', Null, 'Doe')
INSERT INTO mytest (fname, mname, lname) VALUES ('Jane', '', 'Doe')

This makes it possible to do the following distinctions

SELECT * FROM mytest WHERE mname = ''
SELECT * FROM mytest WHERE mname IS NULL

I e, only list those not having a middle name, and list all those where the middle name is unknown.

I haven't encountered any need to do such yet.

There are challenges with this, too.

Disregarding this sample, but in some situations where you currently have two distinct reasons for absence of value, it's easy to represent this with ZLS and Null - but what if a third reason pops up? Perhaps a status field could handle this better, with more flexibility?

The three value logic of this (ZLS/Null/Actual value) will need to be exposed to the user in some way, which means both the interface and the user will need to handle it. Not to mention the future developers/maintainers of the system will need a firm grasp on how these are to be handled, to avoid mixing them.

 

[RuralGuy]

Roy,
Nice test and good question for Doc. Having watched Doc respond to posts, I'll bet he has a decent answer but I haven't a clue what it would be. I do know that Access puts things on block boundries when it Compacts so the test may be skewed a bit.

 

[gemma-the-husky]

interesting topic

i find if there is a possibility that some entries are null, and some are vbnullstring (zls), for a particular field, then you have to be careful how you set criteria for queries, as null and zls are not the same.

therefore, for such fields i will disallow nulls, but allow zls, to ensure all data is homogeneous.

 

[Dennisk]

Iv'e never liked the concept of null.
NULL - I don't Know
ZLS - I know but it is nothing.

I much preferred the xBase way which was the field was EMPTY or not.

 

[The_Doc_Man]

The only reason I prefer ZLS to NULL is that NULL tends to trip up way too many functions. Forcing you to remember to use the NZ function. A ZLS will not trip up anything and you don't need to use NZ if you encounter one. Simplifies code. Further, it is a consistency issue.

If, in your VBA modules, you use Option Explicit (must declare a variable before using it), this is morally equivalent to saying something has to be there for my code to work right. You catch LOGIC errors when you use Option Explicit. You catch LOGIC errors when your program runs into an unexpected NULL. But for production, you had better NOT see something you did not expect. This is my personal view of bullet-proofing code.

There is the issue that if you try to pass a NULL to a string processing subroutine (and I happen to use a lot of such routines) you run into a really nasty problem when that subroutine tries to evaluate string length or extract elements. More error trapping. But if you pass a ZLS as the string argument, the LEN, MID, LEFT, RIGHT, INSTR, and SEG and whatever else you've got will behave consistently with general VBA descriptions of those functions. You don't have to signal an error in a subroutine because your calling routine gave you crappy data it hadn't filtered out on its own.

In the final analysis, I truly dislike using NULL as an indicator of something. Which is why I previously commented on having a separate Y/N field in the example I gave. In my quality control DB, if you check the "Fully Compliant" box, you never test the string anyway.

The experiment shows no discrepancies. If all you have are string descriptors that are either NULL or ZLS, they are the same size. One contains zeros in the string size, string address, and string attributes fields. The other contains zero in size and address but a non-zero attribute field. (Which is how you tell the differnce between NULL and ZLS.) But the string attribute (internal) field does not change size. So to see a database with X number of NULL strings vs. X number of ZLS and see they are the same size does not surprise me in the least.

I guess this is a stylistic issue, but to my viewpoint any bugs associated with ZLS strings such as the autocorrupt in the Browne article and similar such issues are probably due to inconsistency in initial data preparation.

I believe many of you will recognize that my viewpoint of "get it right the first time, it's cheaper" is also consistent with this position.

Yeah, I know - consistency is the hobgoblin of small minds. This small mind needs to be consistent as a matter of habit so that I don't lapse into the habit of writing lax code. Yeah, it's a harsh viewpoint. But what users do to loosely-bulletproofed code is harsh, too.

Call a personal preference if you like.

 

[tehNellie]

Can't argue with you there Doc, I use NULL as little as possible for similar reasons. Yes, there are times when it is preferable to a ZLS and, generally speaking, I use them both as little as possible. If "nothing" or "I don't know" is valid as a general value, why do you have the column in a main table to begin with? (I invoke my own "middle initial" argument to counter this now and to illustrate that there is no such thing as a "general rule").

Under general circumstances though I'd much rather deal with a zls than NULL, far fewer things break when trying to deal with it.

If, in your VBA modules, you use Option Explicit

If you dont, you flippin' well should do

 

[The_Doc_Man]

Thanks, Nellie. By the way, the military solves the "no middle name" issue very easily. If you have no middle name, your middle name is (all lowercase) nmn. End of discussion. We don't keep middle initials. We keep middle names. One of those Congressional mandates that crop up now and then in a military personnel shop. (No, I don't do weapons research. I just assure that folks get paid and that their records follow them when the transfer, get promoted, change status, etc. Just a personnel app.)

There is another part of my explanation regarding ZLS vs. Null. You cannot have a ZLS for a numeric value, so it never occurs. However, if you use strings a lot, it crops up all the time. Since my most recent work has involved a LOT of string parsing, I have to worry about NULL inputs.

Those of you who know the Theory of Finite State Automata will understand that allowing NULL inputs and transitions that operate on NULL inputs very easily makes the automaton Non-Finite. (Translation: POSSIBLE LOOP FOREVER - if you blow the transition diagrams for the NULL case.) For me, avoiding that conversion of Finite Automata to Non-Finite Automata is extremely important in production code. Critical, I might say.

Which is also why I'm a fan of being explicit in numeric data as well. Don't try to encode NULL as a way to say you have no value for something. You really want to have a flag (Yes/No field) that tells you not to process the record in the first place rather than go through complex code only to have to trap it out at the end. Step-by-step validation sounds tedious. (Hell, it IS tedious, no doubt about it.) But when you deal with records that affect someone's livelihood, you don't screw around with code that has to stop at every turn to trap another null.

Nulls? We don't need no stinkin' nulls. (With apologies to The Treasure of Sierra Madre.)

 

[Shep]

Thanks to everyone. This discussion has helped shape my perception of the concepts involved.