David Webb-200187 (8/18/2010)
Redefining NULL on a whim? You can't redefine something that doesn't have a good definition to start with. N/A, unknown, not yet entered, incomplete, etc. can all be valid interpretations of a NULL depending on the context. That's the problem. In order to use it, you have to have a potentially different contextual definition in every table, in some cases multiple definitions per table if you have multiple NULLable columns. Even those can be wrong. Imagine a system where a client's address was NULLable. It might mean that the address had not yet been collected, or it could mean the client was homeless and had no fixed address. The meaning of the NULL address would be ambiguous.I avoid using NULLable columns where practical, but I'm not religious about it.
David, I like the way you think. We may be going down a dead-end road if we try to precisely define what NULL means and that's part of the point of this post.
But what about this - if we can use a nonnumeric NULL in a numeric column, why can't we also show "N/A" or its equivalent? I see how that would be very useful. But I haven't carefully thought it through. What "Gotcha's" would we create if we could use "N/A" in numeric columns? The same problems we have with NULLs maybe? What does 2 + "N/A" = ?
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking