Re: quick FK question

  • From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
  • To: mwf@xxxxxxxx
  • Date: Mon, 21 Dec 2009 09:03:38 -0600

This *is* at the root of the Codd vs. Date debate summarized at
http://www.dbdebunk.com/page/page/1706814.htm.

I am eager to see Chris Date present on this near our office next month.
Enrollment is still open, and there's plenty of space available (wink):
http://method-r.com/education/107-cj-date-course


Cary Millsap
Method R Corporation


On Mon, Dec 21, 2009 at 8:36 AM, Mark W. Farnham <mwf@xxxxxxxx> wrote:

> 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

Other related posts: