Re: Distributed foreign keys (was Re: Category Types)

"Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message news:bd59ns$165s$1_at_gazette.almaden.ibm.com...
> "Bob Badour" <bbadour_at_golden.net> wrote in message
> news:jvkJa.314$s57.50265177_at_mantis.golden.net...
> > "Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message
> [snip]
> > > Having said that, Hugh does have such a constraint in his example:
> > >
> > > Every row in CALLED has a matching row in either EARNS, SALARY_UNK
and
> > > UNSALARIED.
> > > Which does then mean that either SALARY_UNK or UNSALARIED could be
> > > a view over the other two tables. It could be argued that a database
that
> did
> > > not make one of those two a view would be more redundant than one
that
> did.
> >
> > I don't think a view really captures the data.
>
> The view approach is 'information equivalent' with the extra table
approach.
> I.e. the same complete set of information is held or can be derived in the
two
> approaches

This is true. However, the way one gets the data into the database is very different. The triple-relation approach requires one to explicitly state which of the three relations the referenced value belongs in when one first inserts the value. The view approach requires explicit insertion for two of the three relations and prohibits explicit insertion in the third. Or am I missing something?

> > The distributed key requires
> > one to insert the value into two relations. Using a view would require
one
> > to sometimes insert a value into two relations for two of three cases
and
> > prevent one from inserting a value into two relations in the other case.
>
> It's not really a matter of prevention. If a person's salary is unknown,
then
> for a new person, you insert into Person only.

Exactly, and if you did insert into the view, you would get a duplicate error either on the insert to the view or on the insert to Person. The insert is required for two cases and prohibited for the third. I dislike the asymmetry.

> > I find the asymmetry of the view approach unappealing.
>
> I don't like asymmety, but I don't really see any here. If we take the
closed
> world assumption, anything that we don't know anything about does not get
> inserted into the database. A distributed foriegn key should not be
limited to
> the 'complete' case.

Why not? What's the point of a foreign key that says a value might or might not reference a tuple in some relation? Isn't the whole point of a foreign key to state that every value must reference a tuple in some relation? Received on Mon Jun 23 2003 - 03:33:41 CEST