RE: Useful Oracle books - C.J. Date theory vs. practicality

I think that for all practical purposes NULL is "no data entered", which can
be because the value is unknown, does not apply, or just has not been
entered yet.

I started my career on the mainframe.  In the IBM world of yesteryear the
fields on your records were pretty much of a fixed length.  If the data did
not fill the entire field then the field was padded based on the type of
data it was.  In the case of character data this meant blanks (also called
spaces).

Take the example of a 30 character description field.  It no data was
entered you stored 30 spaces so that when the description was presented on a
screen or report spaces would show rather than whatever garbage happened to
be in memory.  But from a business point of view spaces represented "no data
entered" which is NULL as far as I am concerned.

I do not see a problem with the concept of NULL.  Unknowns exist in the real
world all the time. I realize that in design having the possibility of a
indexed column being nullable can cause problems but there are ways around
the problem.  And sometimes the fact the column can be NULL just means there
can be zero or more related rows. In the case of the column being NULL you
know there are no matching join rows for those columns and adding a "column
is not null" statement to your SQL will aid the optimizer is filtering these
rows before it even tries to join on them.  This last point is in Dan's
book.

NULLS avoid the problem of how do you tell if a value entered in a column is
a valid value to be used verse the value is supposed to be an indicator of
no value.  Business rules change over time so the value chosen today may not
be a good choice tomorrow.  The time and money to fix the problem is not
going to be available.  So a system-wide standard no value indicator has
great business value.

As far what do you do when the database manager does not support strong
domains.  How about making heavy use of column level constraints.  Maybe not
a perfect substitute, but the feature really help protects the integrity of
user data when used.

Just my two-cents on the subject of NULLS.
-- Mark D Powell --


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Daniel Fink
Sent: Friday, May 28, 2004 2:35 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Useful Oracle books - C.J. Date theory vs. practicality


Ah, nulls...

One system I worked on had several columns with the following
COLUMN_A VARCHAR2(15) NOT NULL w/Check constraint value in (<list of
values>, 'NULL') ARGH!

I think one of the issues is that many (perhaps most) people do not
understand the implementation of NULL in Oracle. The above 
example is just one of several common 'beliefs' that I have seen. I wonder
how much this has to do with the tendency (at least what 
I perceive) to think of 2-valued logic when it comes to TRUE/FALSE and to
avoid ambiguity. "Is the sun shining?" "Of course it is" 
(when I am sitting in an office where I cannot see out a window). The
'correct' answer is "I don't know, let's go to a window and 
check.", but that seems somewhat ridiculous to actually say in a
conversation.

I started my career as a COBOL programmer. IIRC, I never left a data field
as 'NULL' (I don't even remember if COBOL has such a 
concept). We used high values, low values, spaces, 0, etc. Even the little
bit of C I did years later, uninitialized variables were 
treated as having a value, we just did not know what the value was before
using the variable. With Oracle, NULL represents no value. 
  So, which is closest to the meaning of NULL? Is it "unknown value" or "no
value". In 3-valued logic, it seems that the two are 
equivalent. Is it a matter of semantics? Or am I so off-base that I should
just go back to my bottle of Scotch and stay out of this?

<change topic>
This is why I think this list is so important. A free exchange of ideas,
often with disagreements. Whether you agree or not with the 
other person, you have been exposed to something different and have learned
from it.

To paraphrase Wayne & Garth, "List On, Lex. List On, Dan"

;)

Regards,
Daniel Fink

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: