Oracle-L: RE: NULLS vs. DUMMYING DATA

From: <Paula_Stankus_at_doh.state.fl.us>
Date: Thu, 08 May 2003 10:59:58 -0800
Message-ID: <F001.00594358.20030508105958@fatcity.com>

"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:
INET: Paula_Stankus_at_doh.state.fl.us

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 - 13:59:58 CDT