RE: NULLS vs. DUMMYING DATA

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Thu, 08 May 2003 11:31:43 -0800
Message-ID: <F001.005944B9.20030508113143@fatcity.com>

I happen to think that "dummying up data" is a BAD idea no matter where it is. True, your program has to handle the nulls, but that sure is better than indexing a pile of spaces or 0's.  

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----

[mailto:Paula_Stankus_at_doh.state.fl.us]
Sent: Thursday, May 08, 2003 3:00 PM
To: Multiple recipients of list ORACLE-L

"dummying data"

Guys,

I am not sure what is the best way to deal with this and would like a concensus. If this subject has been talked about before I apologize. The issue is nulls or "dummying" data in an OLTP and also in a Data Warehouse.

-I think that an OLTP system should be considered differently than a data warehouse as it is the source system.
-For the most efficient data warehouses using star schemas, bitmapped indexes, and dimensions I can't see using nulls working as well (even with functional indexes) as you can't control how people will access the data and nulls would invalidate the use of an index - on a large DW this could be very very bad.

-I have a system where the components of a date can be as important as the entire date itself. Therefore, where the components of the date are correct I would keep them and load as part of the date in a date datatype.

Example: February 31, 1999

The concern I have is the month, day, year can all be important depending on the analysis done. So storing these as separate numeric (to cut-down on the junk allowed as input) separately makes sense but would also like to store as date whenever there is date conversion possible. However, what if month and year are correct but day is wrong:

February 31, 1999

-store as missing date in date field (date that actually out of range didn't exist in history)
-then store as separate numeric fields

or
-store as date when I can (no missing date)
-store as separate numeric fields

Ideas?

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Goulet, Dick
  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). Received on Thu May 08 2003 - 14:31:43 CDT