---------- Original Message -----------
From: "sqlsvr" <sqlsvr@...>

> Natural key is an external key (comes from external source), there is
> no need to update outside systems. Autonumbers are not surrogates and
> have nothing to do with the data model. Surrogates are internal DBMS
> implementations such as pointers and not seen by any user of the DBMS.
>
> Using data as a foreign key is NOT a problem. It is the DBMS JOB TO
> translate that foreign key to a pointer lookup. It is a DBMS
> implementation. Users would use foreign keys at the logical level and
> the DBMS would convert them to pointers at the physical level. Your
> talking about letting the users use pointers at the logical level
> which is as stupid as using autonumbers. Using autonumbers is actually
> creating "fake pointers".
>
> Again, pointers should NEVER be made available to the user. It is a
> surrogate key used by the DBMS internally and never seen or used by
> the user. It takes the place of a foreign key internally in the DBMS.

------- End of Original Message -------

Keys, whether candidate or primary, "surrogate" or "natural", are logical
features. Pointers are physical features. Autonumbers, being values, are
logical. Page numbers and record numbers are physical. Many so-called "natural"
keys are just surrogate keys sourced from someone else's system, the
distinction is purely psychological -- consider social security numbers, for
example. I've written about this elsewhere, if not eloquently, and won't repeat
the argumentation further: http://pseudotheos.com/view_object.php?
object_id=1273 (I get no ad revenue, but feel free to find your own good source
on the topic -- maybe EF Codd or CJ Date.)

The database does convert between logical and physical -- that's what an index
does. It just doesn't do it the way you think it should. Consider what would
happen if you needed to drop a foreign key constraint and the referenced master
table while using a pointer-based database -- that would most certainly NOT be
an efficient operation (and maybe impossible,) as the detail table cannot
"stand on its own" anymore. I glean from your experience renaming tables that
you want metadata changes to be efficient? You have to pick your battles.

I mentioned the rdb$db_key trick because under very specific circumstances, it
can help people out. In Oracle, you can use OID's / REF datatype for this. I
haven't personally had a use for them. In Firebird, row id's are not stable and
therefore do not "take the place of a foreign key" at all. AFAIK, that's the
case in every other RDBMS, too. They are purposefully not part of the default
column list when you issue "select *".

You still haven't given us sufficient details to help you with any performance
problems you may or may not have (yet.)

-Philip