There are definitely places where surrogate keys are required, as (I think)
I said.

I have two comments about the proposed candidate key above.  First of all I
have no numbers that would support the claim that a simple integer key
offers performance benefits over a composite key.  I am sure one could
construct an enormously large composite key that would perform poorly, but
based on experience investigating and benchmarking customers' systems over
the years I've never seen a key "in the wild" that was so outrageously big
that it had any measurable adverse performance effect.  Even if it were
measurable, I still wouldn't propose introducing a surrogate key to improve
performance unless it were so bad that it was noticeable.  (Users only
complain about what they can see/feel.)  There are fantastically larger
perfomance gains to be had elsewhere.

Interestingly, about a year ago I spent some time investigating a customer's
request for verification that their composite key components were in the
appropriate order.  (The dogma has always been that it is better to specify
the more selective components first so that they are logically on the left
of the key.)  Even for a very big table and a very big key it was virtually
impossible to discern any benefit from one key order over another.  Ingres
just doesn't seem to be very vulnerable that way.

My second comment is that I would not regard that as a very good candidate
key anyway.  If that were the best I could do given the entity in question I
might very well introduce a surrogate key.  The problem is that LastName and
Address can easily change, and there is no logical necessity why they
shouldn't change in a way that becomes ambiguous.  Therefore the fact that
those three values identify a row at some point in time is a coincidence
rather than a logical necessity.  Depending on the application domain that
might be more or less unlikely, but it could happen.  But if it were very
unlikely, and now that Ingres supports FOREIGN KEY constraints with ON
UPDATE CASCADE, I think I would probably try to use exactly the key you
suggest (assuming there was nothing better).

If I were to introduce a surrogate key I would absolutely *never* assign it
automatically.  I would require the users to go through all kinds of hoops
to absolutely guarantee the proposed new row really does describe a new
entity previously unknown to the system.  But before even resorting to that,
I'd spend a really long time satisfying myself that I really, really
couldn't find a reliable natural candidate key.

As for tediousness of repeating long composite keys, computers don't get
bored.  Programmers might get bored repeatedly writing long join conditions,
but the payoff from using a complete, accurate, and robust logical model
(part of which involves indentifying reliable natural candidate keys and
excluding unnecessary surrogates) is that you need to write way less code
elsewhere.  I am often amazed at how little code I sometimes have to write
once I get rid of some innocent-looking but extraneous attribute.  For
instance, using your example key above, I would have to write no application
code whatever to maintain that (assuming it really is a candidate key).

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"

Then you would be stuck, because there simply is no way to deal with that.
Adding an arbitrary value to the rows in question will allow you to "do
stuff", but since the entire problem is that you don't know what you're
doing the "stuff" to (it's ambiguous), you don't know if the result has any
relationship to reality.  Putting it bluntly, you would just be choosing
rows at random and hoping for the best.

That is rather recherche.  But suppose it's true.  What makes anyone,
including ourselves, think it is reasonable to expect to do sensible,
coherent, everywhere meaningful operations on things we can't identify?  And
what gives us any reason to be confident there really are two or more
"indistinguishable" things in the real world anyway?--after all, we can't
distinguish them!

I don't know if I follow this.  Are you saying we are obliged to try, even
in face of logic, or that we can be excused for saying it can't properly be
done?  If the latter, then I agree; in fact it is our professional duty to
say when something fundamentally can't be done, instead of pretending it can
and hoping no one ever finds out.  (Civil engineers who do that kind of
thing kill people.  Accountants who do it crash stock markets.)

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"