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

"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

> 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.

> 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. In other words, we should have a shorthand for both the following constraints

    Every row in CALLED has *exactly one* matching row

    in either DOES_JOB, JOB_UNK, or UNEMPLOYED.

and

    Every row in CALLED has *at most* one matching row

    in either DOES_JOB or UNEMPLOYED.

I note that a tool such as ERWin already has complete and incomplete sub-type relationships in its logical modelling environment. However, we have no easy way in SQL (AFAIK) of enforcing such constraints.

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Sun Jun 22 2003 - 23:03:23 CEST