Re: One to One Relationship

On 19 Mar 2003, wizofoz2k_at_yahoo.com.au.nospam wrote:

> Most of these have no bearing whatsoever on an FA18, but are
> needed for a C130. They can easily be described as a 1:1 to
> the aircraft type, but are completely optional for exampe for a
> P3-C Orion, which has its own set of unique demands.
>
> So, what do you do? Store the whole kit an kaboodle as
> potential NULL columns in a single AIRCRAFT_TYPE entity? No
> thanks, that's almost impossible to index properly, with all
> those combinations of NULL values. Much better to isolate what
> is common between them all aircraft types into a single table,
> then have "auxiliary" tables on a 1:1 for the specific types
> and their quirks.
>
> This technique used to be called sub-typing. I don't know if
> the term is still "politically correct", but any subtype is
> potentially a 1:1 between two tables. Depending on how you
> choose to implement the sub-type, of course. It can still be
> one table!

Very true. It solves alot of issues having to do with duplicated access code as well as performance of nulls, ..., as well as just makes sense.

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.

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

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.

    With an sql interface, use instead of triggers.

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

-- 
Galen Boyer