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

[Marc Perkowitz]

Title: RE: No Nulls? (was: Warehouse design: snowflake vs star schem

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