RE: quick FK question
Date: Mon, 21 Dec 2009 09:36:39 -0500
Message-ID: <C1A38D29F7E94169847CB935EDBD827C_at_rsiz.com>
This is subject to testing and experimentation to see which way works better with the current technology.
It does raise and interesting question: When faced with incomplete data in columns that are not mere scalars but rather are either part of a key of the current relation or to another relation, is it better to include the tuple in your representation of the relation with some indication the tuple is incomplete or is it better to relegate the incomplete tuple to a staging location until its critical column values are known?
There is meaning added to the the database by having the constraint and a value that declares "this is a provisional value because we don't know the real value" as opposed to merely allowing the column to be null. When the provisional value can be used to drive a process to identify quality problems in the data without discarding the bits of the tuple that are known some good can come out of it. In terms of total work that must be done in an Oracle database to complete the data, this may be superior to having the incomplete tuples in a separate table.
But you mentioned "warehouse," which to a certain extent implies the data is in final form. Will the answers gotten from the warehouse be improved or degraded from including the incomplete tuple? Is there a good general answer to this question? (Am I mistaken or is this at root one of the bits Codd and Date had a mild disagreement about?)
Ah, the quasi-religious war that can be waged over this one!
So, does your "data warehouse modeler" produce models that work well to answer the questions put to it? Does this practice tend to make the models more or less usable?
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Rumpi Gravenstein
Sent: Monday, December 21, 2009 8:01 AM
To: gints.plivna_at_gmail.com
Cc: JSweetser_at_icat.com; oracle-l_at_freelists.org
Subject: Re: quick FK question
<snip>
I've seen once from some duhvelopers - they created all FK columns NOT
NULL. Unfortunately of course there were cases with FK columns where
actual value could not be provided. So what did they do? An obvious
solution! ;) Added one row with id = -1 as a stub to all db tables.
</snip>
Gints I wanted to follow-up on this statement as our warehouse data modeler insists on doing this for all dimension joins claiming that this is good warehouse design as it avoids having to worry about outer joins. How would you respond?
--
Rumpi Gravenstein
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Dec 21 2009 - 08:36:39 CST