RE: quick FK question
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <rgravens@xxxxxxxxx>, <gints.plivna@xxxxxxxxx>
- Date: Mon, 21 Dec 2009 09:36:39 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Rumpi Gravenstein
Sent: Monday, December 21, 2009 8:01 AM
To: gints.plivna@xxxxxxxxx
Cc: JSweetser@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
