Oracle-L: Re: No Nulls? (was: Warehouse design: snowflake vs

From: Ora NT DBA <orantdba_at_netscape.net>
Date: Tue, 15 Oct 2002 06:58:39 -0800
Message-ID: <F001.004E9285.20021015065839@fatcity.com>

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" <pgro_at_bgs.ac.uk>
>Date: 10/15/2002 2:43 AM
>
>Ho Ho Ho - never heard of companies with dead persons on the payroll?
>
>peter
>
>
>
>
>>-----Original Message-----
>>From: dgoulet_at_vicr.com [mailto:dgoulet_at_vicr.com]
>>Sent: 14 October 2002 21:49
>>To: Multiple recipients of list ORACLE-L
>>Subject: Re[2]: No Nulls? (was: Warehouse design: snowflake vs star s
>>
>>
>>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" <ineyman_at_perceptron.com>
>>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
>>ineyman_at_perceptron.com
>>
>>
>>
>> ----- 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 - matt.adams_at_appl.ge.com
>> 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: dgoulet_at_vicr.com [mailto:dgoulet_at_vicr.com]
>> 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" <Rich.Jesse_at_qtiworld.com>
>> 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
>> Rich.Jesse_at_qtiworld.com Quad/Tech
>>International, Sussex, WI USA
>>
>> > -----Original Message-----
>> > From: Robson, Peter [mailto:pgro_at_bgs.ac.uk]
>> > 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: Rich.Jesse_at_qtiworld.com
>>
>> 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: ListGuru_at_fatcity.com (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: dgoulet_at_vicr.com
>>
>> 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: ListGuru_at_fatcity.com (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>END_EMPLOYEMENT date for still employed
>>employees equals to
>>"01/01/4000" (or any other pre-defined date in distant
>>future).</DIV>
>><DIV> </DIV>
>><DIV>Igor Neyman, OCP DBA <A
>>href="mailto:ineyman_at_perceptron.com">ineyman_at_perceptron.com</A
>>
>>
>>> </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">From:
>> <A title=MATT.ADAMS_at_APPL.GE.COM
>>href="mailto:MATT.ADAMS_at_APPL.GE.COM">Adams,
>> Matthew (GECP, MABG, 088130)</A> </DIV>
>> <DIV style="FONT: 10pt arial">To: <A
>>title=ORACLE-L_at_fatcity.com
>> href="mailto:ORACLE-L_at_fatcity.com">Multiple recipients of
>>list ORACLE-L</A>
>> </DIV>
>> <DIV style="FONT: 10pt arial">Sent: Monday, October
>>14, 2002 3:39
>> PM</DIV>
>> <DIV style="FONT: 10pt arial">Subject: RE: No Nulls?
>>(was: Warehouse
>> design: snowflake vs star schem</DIV>
>> <DIV> </DIV>
>> "No application that I can reasonably think
>>of should
>> use NULLS, except those
>>pre-81 <FONT
>> size=2>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? <FONT
>>size=2>----
>> Matt Adams - GE Appliances -
>>matt.adams_at_appl.ge.com
>> 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----- <FONT
>>size=2>From:
>> dgoulet_at_vicr.com [<A
>>
>>href="mailto:dgoulet_at_vicr.com">mailto:dgoulet_at_vicr.com</A>]</F
>>ONT> <FONT
>> size=2>Sent: Monday, October 14, 2002 2:29 PM
>> To:
>> Multiple recipients of list ORACLE-L <FONT
>>size=2>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 <FONT
>>size=2>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 <FONT
>>size=2>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 <FONT
>> size=2>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 <FONT
>>size=2>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"
>><Rich.Jesse_at_qtiworld.com>
>> 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 <FONT
>> size=2>dumminess...) 
>> Rich 
>> Rich
>>
>>Jesse &nb
>>sp; &
>>nbsp; &nb
>>sp; &nb
>>sp;
>> System/Database Administrator <FONT
>>
>>size=2>Rich.Jesse_at_qtiworld.com &n
>>bsp;
>>
>> Quad/Tech International, Sussex, WI USA 
>> > -----Original Message-----
>> >
>> From: Robson, Peter [<A
>>
>>href="mailto:pgro_at_bgs.ac.uk">mailto:pgro_at_bgs.ac.uk</A>]
>> <FONT
>> size=2>> Sent: Monday, October 14, 2002 4:59 AM <FONT
>> size=2>> To: Multiple recipients of list ORACLE-L
>> <FONT
>> size=2>> Subject: RE: Warehouse design: snowflake vs
>>star schemas
>> > > <FONT
>> size=2>> 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
>> <FONT
>> size=2>> wrote to Ralph >
>>Kemball on the subject
>>
>> of business rules and integrity constraints:
>> >
>> > <A target=_blank
>>
>>href="http://www.dbdebunk.com/kimball1.htm">http://www.dbdebun
>>k.com/kimball1.h
>>tm</A>
>> -- Please see the
>>official ORACLE-L
>> FAQ: <A target=_blank
>>
>>href="http://www.orafaq.com">http://www.orafaq.com</A>
>> <FONT
>> size=2>-- Author: Jesse,
>>Rich <FONT
>> size=2> INET: Rich.Jesse_at_qtiworld.com 
>> Fat City Network Services
>>-- 858-538-5051 <A
>>
>> target=_blank
>>href="http://www.fatcity.com">http://www.fatcity.com</A>
>> San Diego,
>> California --
>>Mailing list and web
>> hosting services <FONT
>>
>>size=2>-------------------------------------------------------
>>--------------</
>>FONT>
>> To REMOVE yourself from this mailing list,
>>send an E-Mail
>> message to: ListGuru_at_fatcity.com
>>(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: <A target=_blank
>>
>>href="http://www.orafaq.com">http://www.orafaq.com</A>
>> <FONT
>> size=2>-- Author: <FONT
>>size=2> INET:
>>
>> dgoulet_at_vicr.com 
>> Fat City Network Services
>>-- 858-538-5051 <A
>>
>> target=_blank
>>href="http://www.fatcity.com">http://www.fatcity.com</A>
>> San Diego,
>> California --
>>Mailing list and web
>> hosting services <FONT
>>
>>size=2>-------------------------------------------------------
>>--------------</
>>FONT>
>> To REMOVE yourself from this mailing list,
>>send an E-Mail
>> message to: ListGuru_at_fatcity.com
>>(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).
>></BLOCKQUOTE></BODY></HTML>
>>
>>--
>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>--
>>Author:
>> INET: dgoulet_at_vicr.com
>>
>>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: ListGuru_at_fatcity.com (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
>*********************************************************************
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ora NT DBA
  INET: orantdba_at_netscape.net

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: ListGuru_at_fatcity.com (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).

Received on Tue Oct 15 2002 - 09:58:39 CDT