Re: One to One Relationship

Galen Boyer <galenboyer_at_hotpop.com> wrote in message news:<u4r5z1o3e.fsf_at_standardandpoors.com>...

>
> The one issue is that the "Aircraft Table" now has an id that
> can, by the design of the model, be found in both the P3-C Orion
> subtable as well as the F18 subtable. An aircraft, of course,
> can't be both types, but the model says this is allowed and there
> is no referential integrity rule to protect this from happening.
>

Not in my case. Aircraft type is the entity. That has a unique ID, for a FA18. And a P3. There may now be a row with the same ID in the FA18 table, but ONLY for the FA18 row in the aircraft type table. Same surrogate key ID. Note that these are "types", not actual "elements". Ie, not individual aircraft. Those are handled somewhere else.

> What do you do to solve this data integrity hole? I have some
> ideas, but I'd like to hear others.

I think I know what you mean. Although it doesn't show in the case I mentioned, it does happen. I've got the same problem with mission details off a mission entity. Things like categories, objectives, deviations, status, etc.

>
> My basic ideas are:
>
> With a procedure only interface, the insert logic would be
> something like, have a aircraft_sequence and this gets used
> on insert to the subtables. Before insert triggers on the
> subtables insert the parent row.

I use a proc only interface, but we force the parent to be there before. Ie, we don't create it automagically. Too risky, given that in our case it's an entire mission! What I have in this case (and it's against what I said before for aircrafts!) is a single table with a discriminator column to implement the subtypes. Then, I have views that define each of the sub-types from the main type. The access uses the views for retrieval and even inserts, with auto-enforcement of the value of discriminator. Most of the stuff stored here is free format text anyway, so it makes sense to put it all in a single table.

>
> With an sql interface, use instead of triggers.

Not yet there. First, I've got to solve the problem of getting all this to work with VPDs... ;) But that would be my final target, with an O-R interface. For ease of implementation and more flexibility, I'm currently using a proc only interface. Takes a while.

>
> Both of these still depend on code for RI though. :-(

Sure. And in my case, I've got two types of RI. One is enforced by the database and provides for cascade delete/update via procs, but only deals with reference data. The other is enforced by the database and deals with operational data. No cascade anywhere, can't delete any single line of ops! And so on. All with VPD. Lot of fun...

Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Wed Mar 19 2003 - 17:34:13 CST