It's like watching two gladiators wrestling with a giant anaconda! Seriously though, I'm enjoying (and trying to learn from) the conversation even if I can barely follow parts of it.
Still, you'd have to admit it's easier to put down an anaconda with X+1 gladiators than X gladiators.
About the only thing I might offer an opinion on is that assumptions come in two flavors in experimental statistical analysis: a priori (before the experiment) and a posteriori (after the experiment results are out) so you can call it an a posteriori assumption without any hesitation in my book. The terms are latin http://en.wikipedia.org/wiki/A_priori_and_a_posteriori_(philosophy)
Hmmm... didn't realize that some people would use assumption a posteriori as I'd have thought assumptions are a priori by definition, but hey, you learn something new everyday!
Shooting from the hip, I would say NO. The reason you should default to an empty field is that if you ever took the Len() of a field containing a NULL, you get back NULL, not 0. In my opinion, you NEVER allow a partly populated record to be populated with NULL. Too many functions are not well-behaved when NULL is involved.
As part of the earlier discussion, Banana and I more or less danced around this idea. NULL is NOT a number. It is NOT a string. It is just ... NOT. And therefore it throws a great big monkey wrench into anything that runs into it, with VERY few exceptions.
I offer the opinion that for unused sections of an address you would strongly prefer blanks or empty strings (length 0). While they print the same as a NULL in most cases, the empty string is better-behaved around functions.
In setting up experiments, you make a lot of assumptions before the fact. In interpreting the results, you can make some assumptions after that fact to explain your observations. (In chemistry, you make all sorts of mechanistic assumptions when you get reactions that, when graphed, look like a set of stairs.) My dissertation dealt with one of those. We ended up postulating a reaction with two fast steps and five slow steps. After-the-fact assumptions would tell you which one is rate-determining. (That's chem-speak for "which one is the bottleneck that slows down everything else?")
Interestingly enough, if you follow the methods of Sir Francis Bacon, an assumption after the fact can become the basis for another experiment, in which case it suddenly changes polarity to become a "before the fact" assumption again. Because the Bacon method includes a feedback loop.
Regarding null keys... I honestly have never tried it. I don't think it is allowed. The reason I say that is because when you compare values to NULL you get back NULL. So when Access would try to compare the key value NULL to the key value stored in the index, you would get back an error. I am so deeply against nulls in simple tables that I never allow them. Hence, I've got no clue as to exactly what you would see.
{thick Cajun accent}Mon cher, I don't know what you were wrestlin', but I'm trying to make alligator sauce piquant. So me, I've been working a swamp gator.{/thick Cajun accent}
CraigDolphin, if some of this gave you trouble, you know you can ask for an explanation. As Banana said, most of the time my bark is worse than my bite.
Interesting as I'm actually trying to avoid zero-length string, because it's confusing. However, I haven't considered that functions would work better with a ZLS than a Null. However, ZLS is usually for a string, whereas I usually use functions for number data types... A bit hard to do a Average() on a set of strings, don't you think?
Regarding null keys... I honestly have never tried it. I don't think it is allowed. The reason I say that is because when you compare values to NULL you get back NULL. So when Access would try to compare the key value NULL to the key value stored in the index, you would get back an error. I am so deeply against nulls in simple tables that I never allow them. Hence, I've got no clue as to exactly what you would see.
According to Allen, because Access defaults the Number data type to default vault of '0' and not required, this creates problem when using this field as a foreign key as it can be orphaned, especially if somehow the foreign key field was left Null.
While I feel generally same about having Null, especially if I plan to use a lookup table as a rowsource for a given field, I want my lookup tables to have a back door out in case they don't have the required information, I wonder how you would handle a textbox which has to have something? Tell your users to type in 'Unknown' is a solution, but I have no guarantee that they'll spell it correctly or not use other similar words, impacting data integrity here.
Now that jogs my memory. I recall reading something in distant past saying that you actually could set ZLS on, *and* set it to be required. This would then force the user to explicitly click on the field before moving off the record, thus guaranteeing that the user wasn't too hasty. However, I'd imagine it would be *incredibly* annoying to get an error message because you didn't click a field, let alone a number of fields.
but -- i recently did an app that manages/schedule deliveries. so i have a table of deliveries, and another table of scheduleddeliveries, with deliveryid as fk.
so joining the tables together, a null in the join result indicates an unscheduled delivery. without having to store/manage foreign keys anywhere in the delivery file. If you cancel a schedule just delete the record from the schedule table, and the delivery automatically becomes unscheduled
works really well, and I took banana to be referring to stuff like this in his original posting- where clearly the null is completely worthwhile.
----------
on the other hand if you are trying to use null in a table entry to mean anything other than "undecided" yet, then its possibly not too good a practice
I offer the opinion that for unused sections of an address you would strongly prefer blanks or empty strings (length 0). While they print the same as a NULL in most cases, the empty string is better-behaved around functions.
Having given this some thought I would agree. Logically the unused address fields are not nknown - we know they are not required so they are not unknown.
However, The_Doc_Man and I have come to an agreement that Null shouldn't be assigned a meaning beyond 'no data' and if anyone want to assign an meaning, they should do with a status flag or something to that effect.
In your case, I'd do it the opposite way: Query the delivery table to find records NOT in deliveryschedule table. Not only that gives me positive result (e.g. I have actual data), but also gives me the all records where I need to schedule a delivery. In this case, I'd think that such query would be more informative than a query returning a null.
In your case, I'd do it the opposite way: Query the delivery table to find records NOT in deliveryschedule table. Not only that gives me positive result (e.g. I have actual data), but also gives me the all records where I need to schedule a delivery. In this case, I'd think that such query would be more informative than a query returning a null.
Banana wrote
> Interesting as I'm actually trying to avoid zero-length string, because it's confusing. However, I haven't considered that functions would work better with a ZLS than a Null. However, ZLS is usually for a string, whereas I usually use functions for number data types... A bit hard to do a Average() on a set of strings, don't you think?
I think the majority of Access developers use Null to denote missing information, and share your view on ZLS. Fact is that lot of Access developers consider the change in default value for the Allow Zero Length property between the 97 version and the 2000 version as a bug, and in 2000 and later versions, change all text fields to disallow ZLS, that is, unless one need both Null and ZLS.
I haven't found anything inherently wrong with neither functions, Null, nor for that sake, the combination of them. I think the issue mentioned is what is commonly referred to as PEBKAC - Problem Exists Between Keyboard And Chair There are some developers that apparently lack the ability to handle Null. That's quite OK, but I think it's more fair say so, than blaming functions, Access or Null
So, some (very few), will use ZLS, which will probably make all their functions work for all eternity, just give hard to find/debug wrong results at times, while the rest of us will risk a runtime error if we haven't done our jobs properly (handled Null prior to calling the function). With proper error handling, transactions etc, such runtime error should not cause damages/alterations to stored information.
You'll need to ask your employer what they prefer. 24/7 possibility of wrong results, or risk that a possibility of getting a RT might have snuck into production, providing either correct result, or no result
For the address issue, if they are all for different information, so that it isn't a 1NF issue, then use Null to denote the missing information. That's what Null is there for, and that's what most of us do.
That makes it possible to use Null propagation like this;
to build a full address with new lines only for controls/fields having information - and in a report, for instance use the CanGrow property, to size it appropriately.
With no disrespect intended, Roy, that is NOT what NULL is there for.
NULL is present because of set theory issues. To use NULL to "mean" something makes it no longer a null. My question to you is the same as it would be to anyone else. And the same question that Banana so correctly brought up based on the Wikipedia article about flavors of NULL. What do you do when you suddenly discover that you have TWO meanings for NULL? Or THREE? When is NULL not NULL? (Answer: When you cast a meaning on non-existence.)
I agree with your view that if you take appropriate steps, you can prevent NULL from eating your socks. It is right to protect yourself. But the best defense is a good offense. Wipe out NULL in all records that should otherwise be defined. To me, "partial definition" is exemplary of being unwilling to take the last step to "clean up" the application. Let's stop and think for just a moment. Does it matter to you that much that you have to use IsEmpty rather than IsNull when examining a record?
In the final analysis, you wrote whatever you wrote in your application because you thought it was correct to do so. If you then took the steps to make it work - despite NULL effects - and it doesn't barf on your shoes when it runs, good for you. Nor will I criticize you for having succeeded in any application that allows NULL to exist. I just happen to think that it is not the right way to make things work.
I guess I lied about one thing. It is only MOST of the time that I'm a true pragmatist. In this particular topic, I am a purist. Null doesn't mean anything except "no results returned." To cast another meaning on it is not correct.
I've never run into a shop that had a standard on how to treat ZLS vs. NULL in a text field. In numerics, of course, NULL and 0 do NOT mean the same thing. And in strings, NULL and ZLS don't mean the same thing either. My problem is casting a meaning on something that doesn't exist (NULL). What you want to cast on a ZLS is up to you.
As to PEBKAC, we always used PICNIC - problem in chair, not in computer.
Where are we going here? I don't know, except that I think it is good for people to honestly disagree on this issue. It brings out reasons that many others can read. They can learn by doing so.
I know for an absolute fact that I come on strong when I post. That can sometimes put people off, thinking they cannot argue with me or ask deeper questions. (Thanks, Banana, for helping prove that wrong.)
It's a side effect of writing for the government, really. You have to make everything clear-cut, black & white, no foolishness allowed. I have a pretty good Ops crew in my day job, but in the past there have been operators for whom the only comments start with vulgarity at the "mortal sin" level and go downhill from there. I'm a product of my past history. (Aren't we all?)
So Roy, do what you think you have to do. Just be careful out there and watch out for the villain from The Never-Ending Story. If you are anything like me, you are klutz who can trip over NOTHING.
Thanks Doc_Man. I do know I can ask: it's not that I'm shy about asking, just that I'm still learning the 'full' vocabulary of databases (e.g., 'right joins versus left joins') and it takes a lot of concentrating to follow all the back and forth flying around. I don't like wasting people's time with questions that I can resolve myself by spending some time researching things. And in a discussion like that, I don't think such distraction would be beneficial or conducive to the flow of the discussion itself.
I am still am unclear what the solution is. For example, in a table of addresses, some addresses have a street address (always), and attention-to field (maybe), a rural delivery address number (sometimes), a town/city (always), a state/province (US/Canada but not all countries), a zip-code/postcode (maybe) etc.
How can I construct an address table to avoid the use of 'nulls' if the particular field doesn't apply to the particular address?
Should I have a 'address' table that contains an autonumber key and a 'address_details' table with addressID as a FK, and two field to contain the attribute type descriptor (eg, street, city, rural delivery, zip) and the value (1 Main Street, Kirkland, RD 4, 98240 etc)?
If so, that seems like a lot of work to go to, and makes creating data entry forms for addresses much more time consuming! (I know, I may be the poster child for your lazy developer lament) . And if you have to do this for every table where some fields are only occasionally applicable then there'd potentially be an explosion of *_details tables!
Not that I'm unwilling to change, just not sure where to go from here. I do test for nulls and/or ZLS in my VBA but I do appreciate the wisdom of avoiding the necessity in the first place by better table design.
There are no distinct "better table design" or "this is The Solution". There are opinions and preferences (but of course there are cases of "worse table design" )
Null is implemented relatively consistently through database platforms for the purpose of handling missing information. Most Access developers use that method, including me.
If I where to roll my own method to replace functionality that's already in the database, the inbuilt functionality would have to be seriously flawed for me to consider it worth the effort. I don't think that is the case with Null.
For the Addresses, I would probably keep them in the main table. For those columns where missing information might be an issue, I would use Null to denote this. Null is implemented in relational databases for that purpose.
The method you're describing ("type descriptor" and "value"), to mee, seems to be a variation over the EAV approach (Entity-Attribute-Value - see for instance http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html or http://oracle-wtf.blogspot.com/2006/02/eav-returns-concrete-elephant-approach.html). While lot of people consider EAV a design flaw/error, I see that there can be situations where such approach might be of interest, but I don't see it as a general method to handle missing information. Be prepared that queries in a solution based on the EAV approach might become quite complex and slow compared to more common designs.
If you choose to throw all address fields into a separate table, you'd probably need a Address Type lookup table for the available address types (Street, City, Zip,...). This approach will make it difficult to use the database engine to ensure the mandatory address fields contain information. To have the engine do that job, keep the mandatory fields in the main table, and use the separate address table only for the "Nullable" columns (primary key could probably be a composite primary key consisting of the foreign keys from the main table and the lookup table). If you fall into the temptation of stuffing values for other "Nullable" columns than Address into this table, you're heading towards EAV. If you like same treatment for other "Nullable" columns, then that would probably mean one extra table per "Nullable" column, or "similar columns/information", which is closing in on the next approach.
The real purist approach, can be found in Hugh Darwens article "How To Handle Missing Information Without Using NULL" http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf. I think it's interesting, but a wee bit academic and theoretical to be of practical use, at least until the platforms of choice support the needed new methods/syntax.
I don't like using ZLS to denote missing information because it lacks consistency. It can only be used for some subtypes of the data type text *1), and as far as I know, is not consistently implemented across platforms *2).
But the most important thing, if you're not sure about ZLS vs Null vs other workarounds, try it out. Chose one methods and "eliminate" the others. I use Null, and use code similar to http://allenbrowne.com/bug-09.html to disallow ZLS for all text fields. Test it out for a while. If you're not happy, try the other method (Note - don't try any of this directly in production! Use a copy, or create a dedicated test db). Also, do web searches. There are lots of information out there.
*1) say if you use a fixed width text field with defined size 10, Required = Yes, ZLS = Yes. If nothing is entered (Note - for that to work, you probably need to stuff in a Default Value of "" or something, else you'll probably get a "You must enter value in..." message), using the Len function on the field will returns the same as the defined size of the field -> 10, while toggling the Required and ZLS and same scenario (no Default Value), would give the expected Null.
Roy, regarding your links to ORACLE solution sites: This is just proof of something else that Banana and I have beat pretty hard. When you have database X and have to change to database Y, relying on "side effects" of ZLS or NULL invites total disaster since there is NO guarantee that the new venue will be forgiving.
That's why I have stated many times that if you have potential issues with NULL values, you need a state value that describes whether you think the record is usable as-is or is lacking critical data. Then set the state value and HONOR IT! If you don't, then you will have more bugs than a barrel of industrial strength insect spray could handle.
For CraigDolphin:
How can I construct an address table to avoid the use of 'nulls' if the particular field doesn't apply to the particular address?
Various ways. If you take the USA method of street, apartment or unit or suite, city, county, state, zip, only two of those items can ever be blank - the apartment or unit or suite field, and the city field (for rural addresses). So you can make that blank or ZLS. OR you can include a little yes/no field that tells you if that field is relevant. Or you can have a code that defines "date style" and can work from that code to define you to show your addresses. For UK addresses, I gather you can have more complexity, but the concept is the same.
Let me be clear here. If you feel that the best way to do this is to use a NULL ... AND you are willing to go through the gyrations required to make it work with NULL .. AND there are no other potential "meanings" implied by NULL in this context, go ahead and use it. Keeping in mind that in this thread we have beat to hell-and-gone the ideas of using NULL with any meaning other than "NOTHING is there." If you use NULL to mean NOTHING then at least you are using it in the right context.
What drives me ballistic is when I see "oh, in this case, NULL means we just didn't enter because we didn't think we needed it." Or "NULL here means that we lost some data." Or "NULL signifies that the person didn't answer all of the question." NULL conveys one thing and one thing only - NOTHING. There is no "oh it means xyz." If you use NULL in that narrow sense, fine. If you need to use NULL for any other meaning, don't use nulls. Define a state variable and use THAT to record your interpretive meaning of WHY a field is NULL.
The_Doc_Man wrote
> Roy, regarding your links to ORACLE solution sites: This is just proof of something else that Banana and I have beat pretty hard. When you have database X and have to change to database Y, relying on "side effects" of ZLS or NULL invites total disaster since there is NO guarantee that the new venue will be forgiving.
No, no and NO! There are no mentioning of any problems with Null in those articles, so those articles are in no way proof of your allegations about Null!
The articles discuss the fact that Oracle treats ZLS as Null. Not the other way around. I conclude therefore that ZLS is not consistently implemented across platforms, which I think is a correct and fair conclusion to make. It would not be correct to draw the same conclusions about Null based on those articles. I again state that I dislike ZLS because of this lack of consistancy, and prefer Null, which doesn't suffer such side effects.
I initially was going to disagree with The_Doc_Man about whether a field within a record may have a Null, as I had reasoned that as long the record has a key, it's "present and accounted for", but simply had forgotten its pair of pants.
However, after thinking about it a bit, I realized something. But first I need you to allow me to take you on a little side trip:
Any database designer worth his salt knows that whenever we want our users to fill in a foreign keys, we *always* want to provide them with a combobox or a listbox so they can select valid key to join with the junction record, and if necessary, provide them a way to add a new record, but only after they've at least made a positive effect to acknowledge this is a new record. This is usually satisfied with combobox's auto-expand which try to guess user's input so at least I know that the user has seen the possible options and know that it is there before new record is added. This goes a long way to reduce errors, be it duplicate records or a instance of record with misspelled name or whatever.
Furthermore, I don't know if it is widely implemented, but I always have a "backdoor" entry in my lookup tables; it can be anything like "Unknown", "Undisclosed", "Unspecified", "Not Applicable", or whatever. This gives me a positive result that my users wasn't skipping the fields and know that the record is complete.
Therefore for the address input form, I believe The_Doc_Man mentioned something about having a checkbox. Assuming that street address is a text field, it'd be impractical for obvious reasons to use a combobox here. However, if we used a checkbox or toggle button (distinct from command button, mind you), we could execute code to automatically lock and disable the street address and insert the value "Inapplicable" or whatever it may be.
This method give two benefits; 1) I don't need to worry about my users randomly entering different synonyms for same thing (e.g. N/A, Not Applicable) which would mess up the search results, 2) I am not relying on Nulls, and have a positive result that the field was filled, even though no specific data was provided.
Another thing. We could go a step further and make this even more tighter. Suppose we're storing information about a group of people in a defined region. Therefore, we could easily use comboboxes for ZIP codes which would then auto-fill in cities. However I'd allow the users to edit cities as ZIP codes does not necessarily correspond with city's boundary. I would also break up the street address into fields: House Number, Street Name, which the first one can be a textbox while street name can be a combobox. This would give added assurance that there's no misspelt street or wrong suffix (e.g. a Rd where an Ave should be).
For house number field, it can be now a number type rather than text. Furthermore, in case where it is unknown or inapplicable or whatever, I'd use a negative number to indicate whether it's unknown, inapplicable or something and conditional formatting to display the appropriate text value to the user while saving the negative numbers behind the scene.
But implementing such interface would require lot of work; you'd need to get together the list of possible ZIP codes and cities within a region, then start with major streets and let users add new information as necessary. Would it be worth it? Only you can decide that.
But the idea is that if you *always* requires every and each field to be filled, with appropriate back-doors in place in case where insufficient information is given, I think (need to check with Doc_Man), the database can be said to satisfy the closed world assumption. That is, the database knows everything we need to know about any given records.
You're probably thinking, "But if it can allow values of Unknown, it's not closed world..." Ah, but the value "Unknown" is *information*. You know something about this record, and it's "Unknown".
Null doesn't do that. It's nothing. It can tell you nothing about why this record has no data beyond the fact that there's no data. Why it has no data, Null simply cannot tell you this, and if you are expecting to, then you're making something out of nothing. (bad pun.... )
A tangent: Consider how one would define an object. Suppose that we were asked to define a chair. We could come up with various definitions; let's start with this one:
Code:
a four-legged object designed for sitting upon
. That's a reasonable definition, right? There's other definitions that might work as well but whatever we come up, it's almost always in an assertive fashion; it tells you what it IS, what it can do, and the likes.
Now suppose we were asked to come up with a definition of chair purely in negative terms; what it is NOT. Let's start with one:
Code:
an object not made to be stood upon.
.
Unfortunately, this definition is leaky; as bed isn't made to be stood upon. Therefore, we need to include lying down.
Code:
an object not to be stood upon or laid upon.
But this *still* leaks. There's host of other activities that can be done other than standing upon or lying upon on the chair. By time we've plugged in all leaks, the definition will be so ridiculously long and convoluted, and will attempt to encompass *all* possibility *but* to sit upon.
With this in mind, this is the problem with trying to derive meaning from Nulls; Null in a record cannot tell you why there's no data; it cannot tell you if the user forgot to fill in that field, or user didn't have the data, or it didn't apply or whatever. This is probably why The_Doc_Man repeatedly alluded to a need of using state flags to give *assertive* information about the record.
~~~~~~
Regarding ZLS, I do agree with Roy that ZLS is largely unnecessary, and that Null would be a better placeholder than ZLS. The number data type doesn't have this question at all because there's no "Zero Length Number" which would also be nonsensical. Access defaults the number data type to '0', which is actually more informative than ZLS and Null. Therefore, I prefer to disallow ZLS and force Null.
BUT!
I would much, much, much prefer to use a state flags to fill in a field than rely on Null (and for that matter, ZLS), because status flags can *assert* about something. Null can never do that.
No, no and NO! There are no mentioning of any problems with Null in those articles, so those articles are in no way proof of your allegations about Null!
Roy, 'scuse me but weren't those links at the bottom of your post to articles about how ORACLE treats ZLS as though it were NULL ?
Were we even looking at the same three articles?
My point is that if Jet and ORACLE disagree on how to treat ZLS; and further, ORACLE can think that something is NULL that technically is NOT null; then we have potential chaos on our hands. If you don't like ZLS, don't use it. My advice is that WHATEVER YOU USE, if there can ever be a need to know why it isn't there, you need to track that fact.
Yes. Right viewpoint. It reminds me of a cartoon I once saw in a book on statistical sampling theory and opinion polls. The poll taker with the ubiquitous clipboard is standing perplexed at the door as the interviewee says "... and be sure to mark me as Don't Care, not Don't Know." In the context it was entirely appropriate. And it is appropriate to this train of thought.
Absolutely true. And a point directly to the heart of the matter. Were that field to be NULL, you would not know whether the data gatherer failed to write that value down, or that it is not applicable, or that the interviewee did not know the answer, all three of which are different. Note, there IS one assumption here - that you need to differentiate at least two possible reasons for something not being in a field. If not, then this is a case of NULL having "adequate" meaning i.e. NOTHING. But if you need to do something when you hit a blank, it makes a difference whether you wanted to show "not applicable" or "nobody knew" or "nobody cared" or whatever. So OF COURSE there are those folks who will use NULL here anyway. As long as they don't need it to differentiate into different reasons, they can. As long as it is not an indexed field..., DUP or NO DUP, you should never allow a NULL for any field that is going to be part of an index.
True, and because it is so entirely on point, I forgive the pun.
Ya know, it occurs to me. This thread has been a long discussion about NOTHING. Do you suppose we're turning into a Seinfeld rerun?
For our UK friends who might not be familiar with that series, it was a TV situation comedy ostensibly about "nothing." Which pretty well describes it.
Seriously though, I'm enjoying (and trying to learn from) the conversation even if I can barely follow parts of it.
