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>]</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 target=_blank href="http://www.dbdebunk.com/kimball1.htm">http://www.dbdebunk.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).