> (If you do truly need MI in Java, and you don't want to re-implement all but one of the classes
> you're trying to extend, you wind up having the same problem we're discussing with MI in the
> database, the problem of identity: you'll create a class that implements two interfaces [one
> derived table], contains two attributes that themselves are instances of concrete classes that
> implement those interfaces [two base tables, and foreign keys to them in the derived table], and
> you'll write all the logic to proxy calls through your interfaces into your attributes [instead-

of

> triggers to make the derived view updatable], each of which has its own identity and must be
> careful not to report its own, rather than that of its container, in any of its dealings with

other

> objects [pk on the derived table vs. pk on the two base tables].)

Maybe I was the only one not thinking straight on this, but this is actually less of a problem in a
relational database than in the in-memory object world. Here's why:

If we start with:

table A (A_id, A_x, A_y, A_z)
table B (B_id, B_x, B_y, B_z)

And then derive with:

table C (C_id, C_x, C_y, C_z, C_a_id, C_b_id)

We usually think that we have an identity problem: something that refers to items in B doesn't seem
to be referring to items in C, even though all C's are B's (and they are A's as well.) In the
object world, you wind up with this problem when an object has two member variables that each have
their own, separate identity, and they know their own identity but not necessarily that of their
container (as a workaround for the lack of MI.) But we don't have to do that in the relational
world!

If a C "is" an A and a B, then the C_a_id and C_b_id fields will be not-null foreign keys;
furthermore, they will each be unique, as no two C's can "be" the same A or the same B. As a
result, C_a_id and C_b_id are both candidate keys (not-null, unique -- just not chosen as primary)
which means that logically, they're just as good at identifying rows in C as C_id, which people
focus all their attention on because they think of it (especially because of generators) as a
pointer, a memory address. In fact, you could drop C_id, and you still have two candidate keys on
that table; C_id may just be convenient for other reasons. If you need a foreign key to C, you can
take your pick -- any of the three fields would work, though they each are more or less useful
depending on the types of short-circuit joins you may want to do later.

But yes, there's still a semantic mess when it comes to an updatable view (C_everything) that joins
A, B, and C together -- does a change to C_everything.C_a_id update A.A_id, or just C.C_a_id? Does
a delete from C_everything just delete from C, or delete from A and B as well? It's convenient to
have a rule (as you stated) of "no gender changes", but that also takes away an important choice
from the data modeller. I do think it would be nice to provide a mechanism for setting all of this
up that gives you, say, the options of "changes to C affect A and B" vs. "changes to C are stand-
alone", and implement the insert/update/delete logic consistently in each case. I would expect it's
rare to have pass-through updates (changing C_b_id also updates B.B_id) without pass-through
deletes (deleting from C deletes from B as well).

-Philip