Re: No Nulls? (was: Warehouse design: snowflake vs

Tue, 15 Oct 2002 07:19:41 -0700

Sounds like they needed a business rule, enforced by a constraint saying the date of death
must be less than or equal to sysdate :-).

John

Toepke, Kevin M wrote:
I was once a consultant on a large Payroll project and ran into a case where
someone had entered a date of death 1 year in advance (something like
8/17/1999 instead of 8/17/1998). I found it in the middle of November.
Because of this, the person was still getting paid. I sent email to payroll,
HR and my manager questioning this "planned death"! Shortly thereafter I
left the project for about 3 months and when I came back the data hadn't
been changed!

-----Original Message-----
Sent: Tuesday, October 15, 2002 10:04 AM
To: Multiple recipients of list ORACLE-L


See logical, isn't it!!  *-)

Dick Goulet

____________________Reply Separator____________________
Author: "Robson; Peter" <[EMAIL PROTECTED]>
Date:       10/15/2002 2:43 AM

Ho Ho Ho - never heard of companies with dead persons on the payroll?

peter


  
-----Original Message-----
Sent: 14 October 2002 21:49
To: Multiple recipients of list ORACLE-L


I'll agree with Igor.  Actually my 'preferred' option would 
be to use their
birth date + 80 years which is the generally accepted life 
expectancy of a human
being.  Lets face it, you aren't going to employ the guy/girl 
after their dead! 
And if their not dead by then, then sure as heck they'll be retired.

Dick Goulet

____________________Reply Separator____________________
Author: "Igor Neyman" <[EMAIL PROTECTED]>
Date:       10/14/2002 12:14 PM

RE: No Nulls? (was: Warehouse design: snowflake vs star 
schemEND_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 5.50.4731.2200" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<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>]</F
ONT> <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         &nb
sp;  &
nbsp;         &nb
sp;  &nb
sp; 
  System/Database Administrator</FONT> <BR><FONT 
  
size=2>[EMAIL PROTECTED]     &n
bsp;  
      
  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 target=_blank 
  
href=""http://www.dbdebunk.com/kimball1.htm">http://www.dbdebun
k.com/kimball1.h
tm</A></FONT> 
  <BR><FONT size=2>-- </FONT><BR><FONT size=2>Please see the 
official ORACLE-L 
  FAQ: <A target=_blank 
  
href=""http://www.orafaq.com">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

  target=_blank 
href=""http://www.fatcity.com">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 target=_blank 
  
href=""http://www.orafaq.com">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

  target=_blank 
href=""http://www.fatcity.com">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></BODY></HTML>

-- 
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).

    



*********************************************************************
This  e-mail   message,  and  any  files  transmitted   with  it, are
confidential  and intended  solely for the  use of the  addressee. If
this message was not addressed to  you, you have received it in error
and any  copying,  distribution  or  other use  of any part  of it is
strictly prohibited. Any views or opinions presented are solely those
of the sender and do not  necessarily represent  those of the British
Geological  Survey. The  security of e-mail  communication  cannot be
guaranteed and the BGS  accepts no liability  for claims arising as a
result of the use of this medium to  transmit messages from or to the
BGS. The BGS cannot accept any responsibility  for viruses, so please
scan all attachments.                            http://www.bgs.ac.uk
*********************************************************************

Reply via email to