Re: Surrogate Keys

>> That's a little harsh. Access is fine for many database
applications. Its not client/server, but many small applications don't benefit from that anyway. Until one knows the scope of your pplication, it is difficult to advise. <<

My objection is not that it is not client/server; my objection is that it is not SQL, that is is buggy, and runs poorly. if you need a desktop DB, then you find much better ones. People use ACCESS because it came loaded on their machine, not because it s a ratinal decision.

>> All databases I know of have an autonumbered datatype which is
perfect for use as a primary key. <<

Ever hear of DB2? You will find that the better products will have genrator functions for a sequence, not put it in as a data type.

>> If you have a data entry validation need you might follow this
advice, but I would use the resulting data as an alternate key. <<

When you say "alternate key", I think of things like the old part number instead of the new part number. That is, a key which still has some meaning for a person searching the database as opposed to a totally meaningless and redundant column.

>> Autonumbered surrogate primary keys make life really easy in all
sorts of ways. <<

A true surrogate key would be maintained by the system and not exposed to the users. What you have is an artifacial key that messes up the system. I have see people write stuff liek this:

 CREATE TABLE Foobar
 (seqkey IDENTITY PRIMARY KEY,
  realkey1 INTEGER NOT NULL,
  realkey2 INTEGER NOT NULL,
  UNIQUE (realkey1, realkey2),
   ...);

but all this does is waste space.

>> Debatably, they improve performance too, but I have seen contrary
arguments on that one. <<

I think that you would have to measure that one on a case by case basis.

--CELKO--
Joe Celko, SQL and Database Consultant

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon May 29 2000 - 00:00:00 CEST