Re: How to ensure data consistency?

On Tue, 07 Sep 2004 23:24:47 -0700 ddtl wrote: s> The trigger cannot say: "hey, indeed your quiery is correct and I
> allowed it to pass, but you didn't fill in additional properties into
> the sub-table" - after the row is stored, the trigger is gone.

Ups, that's something I completely missed. The problem that data has to be filled into the main table *and* a specialization table (or sometimes not, in your case) also applies to my proposed alternative solution. I assume, however, that there are ways to deal with such cases. Unfortunately, at the moment, I'm a bit limited in time to check that myself.

> >Another approach would be using a different data model, illustrated
> >below. However, there still needs to be validation (with triggers?)
> >that the id's of the specified specialization are non null.
> >
> >TS
> >t_id (PK)
> >specialization_type: Either "a", "b", or "c".
> >b_id (FK)
> >c_id (FK)
> >[...]
>
> Is it the main (super) table?

TS is the main table (I added an S because I prefer tables to have names in plural).

> If I understand correctly, you want to put into each row number of
> columns for common properties and then add to it N additional columns
> where N=(number of groups with additional properties), and every one
> of those is FK?

Yes.

> If that is so, it won't work (try it!).

I just tried it: It seems to work (didn't try implementing any checks, though). What makes you believe that it doesn't?

Felix Received on Tue Sep 07 2004 - 23:56:58 CEST