Re: How to ensure data consistency?

"ddtl" <fake_at_address.com> wrote in message news:3fsrj0lpe76pfvfjfe1ur2ubpb3hqu69fg_at_4ax.com...
>
> >Try a Google search on "generalization specialization relational design".
> >
> >After looking at some of the websites, come back and tell us if they
> >helped, and if you still have questions.
>
> Thanks, I found what I was looking for - which is not what I hoped to
find,
> though - according to that article:
>
>
http://www.tomjewett.com/dbdesign/dbdesign.php?page=subclass.php&imgsize=largest
>
> it is impossible to enforce specialization constraints, and the only way
to
> ensure that every item with additional properties in the super-table has
> an entry in the appropriate sub-table, is in the front end and not in a
> database itself :-(.
>

Not necessarily true.

If the DBMS supports deferred constraint mechanisms, I circular Foreign Key constraint relationship will work. This type of answer typically evokes howls of protest because for some reason, individuals have this notion that circular relationships are bad (which is probably true when involving keys and a DBMS that doesn't support deferred constraints). Nonetheless, such a of pair a referential relationships are logically consistent (take the iff relationship for a definition for example) in some real world cases.

Look up Celko's recent posts in this newsgroup for a demonstration on how to enforce mutual exclusivity in a generalization/specialization hierarchy or lattice. He uses a "discriminator" value appended to the natural key to form an alternative key. Each specialization table will reference this key and should have a constraint limiting the value of the discrimator to a single value. His example relates to specializations on types of cars (SUV, sedan, etc.).

Regards,

Dan Received on Wed Sep 08 2004 - 07:17:15 CEST