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