Re: One to One Relationship

Following up on Galen Boyer, 20 Mar 2003:

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

Of course it can! To units, for example. And countries. And other materiel types. And missions. But the actual aircraft itself (as identified by the tail number) is kept somewhere else. It has other relationships. When aircraft type id is used as FK in another table, it is the one from the aircraft type table. Subtype tables (fa18, c130, etc) are not involved here. There is a column in aircraft type that says which subtype table can I get the details from. But you are correct: aircraft type is mostly lookup and reference.

>
> My problem is that the a particular subtype can have
> relationships with a bunch of other subtypes, as well as other
> entities in other relationship hierarchies. (A portfolio of
> financial instruments can contain ids at many different levels in
> the finance hierarchy). The best way I think I can represent
> this is to have a relationship table hang off the appropriate
> parent's table.

Lessee if I can visualize this correctly. Forgive me if I'm completely out: these things can be very complex. (Usually takes me a week or so with a BA before I can say I fully understand what's going on.)

Most likely I'll be WAY off. Here goes anyway, in the spirit of cooperation and communication.

portfolio of fin instruments is your supertype? then you have potentially many types of loans hanging off the portfolio?

If so, the keys for loan types could be portfolio key+their own surrogate key, no?

And the key used to link these subtypes in turn to many others in the hierarchy would be the surrogate key of each subtype.

I found I need to use surrogate keys once I get into this sort of stuff. They make life a LOT easier. Dave Ensor in his design books makes a good case for surrogate keys, but I feel not strong enough in the case of subtypes - they are ideal to support them and hierarchies.

I used to be on the side of not using surrogates unless absolutely necessary. Nowadays, I go for them whenever I can. They make complex hierarchical relationships a breeze. And multi-level subtype hierarchies equally easy. Date and Pascal hate them, but they make a lot of sense when a database does not support all the useful bits of the relational model.

Your experience?

> But, then, a commercial loan and a personal loan
> would have the same key found in the loan table.

If you use surrogate keys, no. They would have each their own key, and a fk to portfolio or loan. But their surrogate key would be enough to other entities hanging from them.

> Some other
> entity, maybe porfolio could be a collection of any derivatives
> of those things. Then, have a union view to bring them all
> back nice and tidy.

In a way, a subtype is a special case of an arc. (or vicky-the-versa, can never remember which...). As soon as you have an arc, you're talking unions. Nothing wrong with that. Used to be a problem with earlier versions, but since V8 it isn't anymore.

I know you know this, but the reason I state it is I've heard many times "arcs and unions should be eliminated from proper Oracle design for efficiency reasons". That used to be the case.

Not anymore! I've had 5-way unions that return rows instantaneously, with all indexes firing off on the explain plan. But I need 8.1.7.4 for that.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam