Allow me to put one more point into the discussion, that being in my experience
NULLS invite updates. By that I mean, and I'll borrow from my 'favorite' ERP
application namely PeopleSoft.
PeopleSoft does not by itself allow NULLS, replacing them with a single space,
but one particular table has a column of type DESC VARCHAR2(255) that gets
initially populated by a PeopleSoft NULL. This seems to be a panel processor
activity because none of our developers can find out why. It is subsequently
updated with any number of characters and seeing that PeopleSoft is not too
bright on setting storage parameters, ESPECIALLY pctfree and pctused, we have
99% of the table chained into multiple blocks, even though the average row size
if only 312 bytes!
Therefore, kill the NULL because NULLS invite updates and chaining.
Dick Goulet
____________________Reply Separator____________________
Author: "Marc Perkowitz" <[EMAIL PROTECTED]>
Date: 10/14/2002 2:09 PM
RE: No Nulls? (was: Warehouse design: snowflake vs star schemThis is true. But
you still need to add logic to your application to suppress displaying the
termination date when it is = "01/01/4000". I can pretty well guarantee your
users will not like seeing a "dummy" date on their reports and screens.
And in this case, you cannot use sysdate, as they will want to see if someone is
scheduled for termination in the near future, say for one of the too frequent
layoffs that are happening these days.
You will also need to protect against someone inputting this value or a higher
value as that will complicate things.
The point is that using defaults or dummy values is not a pain-free alternative
to nulls. If you do use these, be sure to be consistent and, as usual, document
these. Probably a good approach, if you use PL/SQL, is to create a
package-level variable that can be used globally. That way you can avoid using
hard-coded values. You could also use this approach with languages that are
using embedded SQL or ODBC/JDBC. Alternatively, you could use a construct
within the language like Java's final static constants.
Marc Perkowitz
----- Original Message -----
From: Igor Neyman
To: Multiple recipients of list ORACLE-L
Sent: Monday, October 14, 2002 4:10 PM
Subject: Re: No Nulls? (was: Warehouse design: snowflake vs star schem
Actually, you don't have to deal with "01/01/4000" date (at least on
"select"), all you have to do in order find currently employed employees, is:
where END_EMPLOYMENT > sysdate
as for inserts, all you have to do, is define "01/01/4000" as a default value
for END_EMPLOYMENT,
also, not allowing NULLs, makes it easier for indexing.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
----- Original Message -----
From: Fink, Dan
To: Multiple recipients of list ORACLE-L
Sent: Monday, October 14, 2002 4:49 PM
Subject: RE: No Nulls? (was: Warehouse design: snowflake vs star schem
The problem I see with NO NULLS is that artificial data must be created,
where the data is truly not known. Whether you deal with NULLs or artificial
data, you will always have to code accordingly, so it is a wash. Igor's example
is an good one. When I write an app to access the END_EMPLOYMENT date, I must
handle a date of '01/01/4000'. Or I can handle the NULL condition. As a person
who has had to support some very convoluted code, I'd rather deal with NULL.
What if the employee record contained TERM_CODE? I would rather have the value
NULL, meaning they have not been terminated rather than dealing with hard-coded
or lookup values.
-----Original Message-----
From: Igor Neyman [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 14, 2002 2:14 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: No Nulls? (was: Warehouse design: snowflake vs star schem
END_EMPLOYEMENT date for still employed employees equals to "01/01/4000"
(or any other pre-defined date in distant future).
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
----- Original Message -----
From: Adams, Matthew (GECP, MABG, 088130)
To: Multiple recipients of list ORACLE-L
Sent: Monday, October 14, 2002 3:39 PM
Subject: RE: No Nulls? (was: Warehouse design: snowflake vs star schem
"No application that I can reasonably think of should
use NULLS, except those pre-81
where there are obsolete columns."
Everytime somebody says this to me, I ask them:
How do you handle still employed employees in an EMPLOYEE table
that contains a END_EMPLOYEMENT date column?
What's your take?
----
Matt Adams - GE Appliances - [EMAIL PROTECTED]
Write a poem about a haircut! But lofty, noble, tragic, full of love,
treachery, retribution, quiet heroism in the face of certain doom!
Six lines, cleverly rhymed, and every word beginning with the letter s!
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 14, 2002 2:29 PM
To: Multiple recipients of list ORACLE-L
Subject: Re:No Nulls? (was: Warehouse design: snowflake vs star schem
Jesse,
I'll refrain from personal comments, but on CJ's quote, he's
correct. Nulls
are an oddity. They cannot be true or false (<column_name> = NULL or
<column_name> != NULL), nor can they equal anything. They are in effect
a third
logical state of nothingness. You also have to code most applications
with
indicator variables to check for their existence. All in all a real
pain in the
backside. BUT, if you give me the possibility that nulls exist in the
data I
much prefer using them vs. many a third party solution of a single
space. No
application that I can reasonably think of should use NULLS, except
those pre-81
where there are obsolete columns.
Dick Goulet
____________________Reply Separator____________________
Author: "Jesse; Rich" <[EMAIL PROTECTED]>
Date: 10/14/2002 9:33 AM
On the link below is this quote from C.J.Date:
"I don't want you to think that my SQL solution to your problem means I
advocate the use of nulls. Nulls are a disaster."
Of course, he doesn't expound upon it (probably not a need except for
dummies like me). Anyone care to comment? (On the quote, not on my
dumminess...)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI
USA
> -----Original Message-----
> From: Robson, Peter [mailto:[EMAIL PROTECTED]]
> Sent: Monday, October 14, 2002 4:59 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Warehouse design: snowflake vs star schemas
>
>
> Just for the record (and perhaps to confirm that there are
> always two sides
> to a story). Readers may like to see the article Chris Date
> wrote to Ralph
> Kemball on the subject of business rules and integrity constraints:
>
> http://www.dbdebunk.com/kimball1.htm
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: No Nulls? (was: Warehouse design: snowflake vs star
schem</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1106" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>This is true. But you still need to add logic
to your application to suppress displaying the termination date when it is =
"01/01/4000". I can pretty well guarantee your users will not like
seeing a "dummy" date on their reports and screens.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>And in this case, you cannot use sysdate, as they
will want to see if someone is scheduled for termination in the near future, say
for one of the too frequent layoffs that are happening these days.
</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>You will also need to protect against someone
inputting this value or a higher value as that will complicate
things.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>The point is that using defaults or dummy values is
not a pain-free alternative to nulls. If you do use these, be sure to be
consistent and, as usual, document these. Probably a good approach, if you
use PL/SQL, is to create a package-level variable that can be used
globally. That way you can avoid using hard-coded values. You
could also use this approach with languages that are using embedded
SQL or ODBC/JDBC. Alternatively, you could use a construct within the
language like Java's final static constants.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Marc Perkowitz</FONT></DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT:
#000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A [EMAIL PROTECTED] href="mailto:[EMAIL PROTECTED]";>Igor
Neyman</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A [EMAIL PROTECTED]
href="mailto:[EMAIL PROTECTED]";>Multiple recipients of list ORACLE-L</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Monday, October 14, 2002 4:10
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: No Nulls? (was: Warehouse
design: snowflake vs star schem</DIV>
<DIV><BR></DIV>
<DIV><FONT size=2>Actually, you don't have to deal with "01/01/4000" date (at
least on "select"), all you have to do in order find currently employed
employees, is:</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>where END_EMPLOYMENT > sysdate</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>as for inserts, all you have to do, is define
"01/01/4000" as a default value for END_EMPLOYMENT,</FONT></DIV>
<DIV><FONT size=2>also, not allowing NULLs, makes it easier for
indexing.</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV>Igor Neyman, OCP DBA<BR><A
href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</A><BR>
</DIV>
<DIV> </DIV>
<DIV> </DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT:
#000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color:
black"><B>From:</B>
<A [EMAIL PROTECTED] href="mailto:[EMAIL PROTECTED]";>Fink, Dan</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A [EMAIL PROTECTED]
href="mailto:[EMAIL PROTECTED]";>Multiple recipients of list ORACLE-L</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Monday, October 14, 2002 4:49
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: No Nulls? (was: Warehouse
design: snowflake vs star schem</DIV>
<DIV><BR></DIV>
<DIV><SPAN class=678533919-14102002><FONT face=Arial color=#0000ff
size=2>The problem I see with NO NULLS is that artificial data must be
created, where the data is truly not known. Whether you deal with NULLs or
artificial data, you will always have to code accordingly, so it is a wash.
Igor's example is an good one. When I write an app to access the
END_EMPLOYMENT date, I must handle a date of '01/01/4000'. Or I can handle
the NULL condition. As a person who has had to support some very convoluted
code, I'd rather deal with NULL. What if the employee record contained
TERM_CODE? I would rather have the value NULL, meaning they have not been
terminated rather than dealing with hard-coded or lookup
values.</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Igor Neyman
[mailto:[EMAIL PROTECTED]]<BR><B>Sent:</B> Monday, October 14, 2002
2:14 PM<BR><B>To:</B> Multiple recipients of list
ORACLE-L<BR><B>Subject:</B> Re: No Nulls? (was: Warehouse design:
snowflake vs star schem<BR><BR></FONT></DIV>
<DIV><FONT size=2>END_EMPLOYEMENT date for still employed employees equals
to "01/01/4000" (or any other pre-defined date in distant
future).</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV>Igor Neyman, OCP DBA<BR><A
href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</A><BR>
</DIV>
<DIV> </DIV>
<DIV> </DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px;
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color:
black"><B>From:</B>
<A [EMAIL PROTECTED]
href="mailto:[EMAIL PROTECTED]";>Adams, Matthew (GECP, MABG,
088130)</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A [EMAIL PROTECTED]
href="mailto:[EMAIL PROTECTED]";>Multiple recipients of list
ORACLE-L</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Monday, October 14, 2002 3:39
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: No Nulls? (was:
Warehouse design: snowflake vs star schem</DIV>
<DIV><BR></DIV>
<P><FONT size=2>"No application that I can reasonably think of should
</FONT><BR><FONT size=2>use NULLS, except those pre-81</FONT> <BR><FONT
size=2>where there are obsolete columns."</FONT> </P>
<P><FONT size=2>Everytime somebody says this to me, I ask them:</FONT>
</P>
<P><FONT size=2>How do you handle still employed employees in an
EMPLOYEE table </FONT><BR><FONT size=2>that contains a END_EMPLOYEMENT
date column?</FONT> </P>
<P><FONT size=2>What's your take?</FONT> <BR><FONT size=2>----</FONT>
<BR><FONT size=2>Matt Adams - GE Appliances -
[EMAIL PROTECTED]</FONT> <BR><FONT size=2>Write a poem about a
haircut! But lofty, noble, tragic, full of love, </FONT><BR><FONT
size=2>treachery, retribution, quiet heroism in the face of certain
doom! </FONT><BR><FONT size=2>Six lines, cleverly rhymed, and every word
beginning with the letter s!</FONT> </P>
<P><FONT size=2>-----Original Message-----</FONT> <BR><FONT size=2>From:
[EMAIL PROTECTED] [<A
href="mailto:[EMAIL PROTECTED]";>mailto:[EMAIL PROTECTED]</A>]</FONT>
<BR><FONT size=2>Sent: Monday, October 14, 2002 2:29 PM</FONT> <BR><FONT
size=2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT
size=2>Subject: Re:No Nulls? (was: Warehouse design: snowflake vs star
schem</FONT> </P><BR>
<P><FONT size=2>Jesse,</FONT> </P>
<P><FONT size=2> I'll refrain from personal comments,
but on CJ's quote, he's correct. Nulls</FONT> <BR><FONT size=2>are
an oddity. They cannot be true or false (<column_name> =
NULL or</FONT> <BR><FONT size=2><column_name> != NULL), nor can
they equal anything. They are in effect a third</FONT> <BR><FONT
size=2>logical state of nothingness. You also have to code most
applications with</FONT> <BR><FONT size=2>indicator variables to check
for their existence. All in all a real pain in the</FONT>
<BR><FONT size=2>backside. BUT, if you give me the possibility
that nulls exist in the data I</FONT> <BR><FONT size=2>much prefer using
them vs. many a third party solution of a single space. No</FONT>
<BR><FONT size=2>application that I can reasonably think of should use
NULLS, except those pre-81</FONT> <BR><FONT size=2>where there are
obsolete columns.</FONT> </P>
<P><FONT size=2>Dick Goulet</FONT> </P>
<P><FONT size=2>____________________Reply
Separator____________________</FONT> <BR><FONT size=2>Author: "Jesse;
Rich" <[EMAIL PROTECTED]></FONT> <BR><FONT
size=2>Date: 10/14/2002 9:33
AM</FONT> </P>
<P><FONT size=2>On the link below is this quote from C.J.Date:</FONT>
</P>
<P><FONT size=2>"I don't want you to think that my SQL solution to your
problem means I</FONT> <BR><FONT size=2>advocate the use of nulls.
Nulls are a disaster."</FONT> </P>
<P><FONT size=2>Of course, he doesn't expound upon it (probably not a
need except for</FONT> <BR><FONT size=2>dummies like me). Anyone
care to comment? (On the quote, not on my</FONT> <BR><FONT
size=2>dumminess...)</FONT> </P><BR>
<P><FONT size=2>Rich</FONT> </P><BR>
<P><FONT size=2>Rich
Jesse &
nbsp; &nb
sp;
System/Database Administrator</FONT> <BR><FONT
size=2>[EMAIL PROTECTED]
Quad/Tech International, Sussex, WI USA</FONT> </P>
<P><FONT size=2>> -----Original Message-----</FONT> <BR><FONT
size=2>> From: Robson, Peter [<A
href="mailto:[EMAIL PROTECTED]";>mailto:[EMAIL PROTECTED]</A>]</FONT> <BR><FONT
size=2>> Sent: Monday, October 14, 2002 4:59 AM</FONT> <BR><FONT
size=2>> To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT
size=2>> Subject: RE: Warehouse design: snowflake vs star
schemas</FONT> <BR><FONT size=2>> </FONT><BR><FONT size=2>>
</FONT><BR><FONT size=2>> Just for the record (and perhaps to confirm
that there are </FONT><BR><FONT size=2>> always two sides</FONT>
<BR><FONT size=2>> to a story). Readers may like to see the article
Chris Date </FONT><BR><FONT size=2>> wrote to Ralph</FONT> <BR><FONT
size=2>> Kemball on the subject of business rules and integrity
constraints:</FONT> <BR><FONT size=2>> </FONT><BR><FONT size=2>>
<A href="http://www.dbdebunk.com/kimball1.htm";
target=_blank>http://www.dbdebunk.com/kimball1.htm</A></FONT> <BR><FONT
size=2>-- </FONT><BR><FONT size=2>Please see the official ORACLE-L FAQ:
<A href="http://www.orafaq.com";
target=_blank>http://www.orafaq.com</A></FONT> <BR><FONT size=2>--
</FONT><BR><FONT size=2>Author: Jesse, Rich</FONT> <BR><FONT
size=2> INET: [EMAIL PROTECTED]</FONT> </P>
<P><FONT size=2>Fat City Network Services --
858-538-5051 <A href="http://www.fatcity.com";
target=_blank>http://www.fatcity.com</A></FONT> <BR><FONT size=2>San
Diego, California -- Mailing
list and web hosting services</FONT> <BR><FONT
size=2>-----------------------------------------------------------------
----</FONT>
<BR><FONT size=2>To REMOVE yourself from this mailing list, send an
E-Mail message</FONT> <BR><FONT size=2>to: [EMAIL PROTECTED] (note
EXACT spelling of 'ListGuru') and in</FONT> <BR><FONT size=2>the message
BODY, include a line containing: UNSUB ORACLE-L</FONT> <BR><FONT
size=2>(or the name of mailing list you want to be removed from).
You may</FONT> <BR><FONT size=2>also send the HELP command for other
information (like subscribing).</FONT> <BR><FONT size=2>--
</FONT><BR><FONT size=2>Please see the official ORACLE-L FAQ: <A
href="http://www.orafaq.com";
target=_blank>http://www.orafaq.com</A></FONT> <BR><FONT size=2>--
</FONT><BR><FONT size=2>Author: </FONT><BR><FONT size=2> INET:
[EMAIL PROTECTED]</FONT> </P>
<P><FONT size=2>Fat City Network Services --
858-538-5051 <A href="http://www.fatcity.com";
target=_blank>http://www.fatcity.com</A></FONT> <BR><FONT size=2>San
Diego, California -- Mailing
list and web hosting services</FONT> <BR><FONT
size=2>-----------------------------------------------------------------
----</FONT>
<BR><FONT size=2>To REMOVE yourself from this mailing list, send an
E-Mail message</FONT> <BR><FONT size=2>to: [EMAIL PROTECTED] (note
EXACT spelling of 'ListGuru') and in</FONT> <BR><FONT size=2>the message
BODY, include a line containing: UNSUB ORACLE-L</FONT> <BR><FONT
size=2>(or the name of mailing list you want to be removed from).
You may</FONT> <BR><FONT size=2>also send the HELP command for other
information (like subscribing).</FONT>
</P></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>
