Re: Surrogate Keys

Joe Celko wrote:

> >> There are so many related tables in my database that I am running up
> against Access 97's 10-field limit on primary keys. One obvious
> solution is to use a surrogate key, i.e., instead of using the "real"
> fields that should compose the primary key, use instead a made-up
> number to serve as the primary key. Is this a reasonable solution? Any
> suggestions on alternate soluation would be appreciated. <<
>
> In order of preference:
>
> 1) Get rid of ACCESS; it stinks anyway and will just get you in trouble

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 application, it is difficult to advise.

>
>
> 2) Redesign the database -- I have never seen a ten column primary key
> in my life.
 

>
> 3) Use a hashing function on those columns to build the surrogate key
>
> 4) Generate a random number with a check digit and use it as the key.

This is a waste of time. All databases I know of have an autonumbered datatype which is perfect for use as a primary key. If you have a data entry validation need you might follow this advice, but I would use the resulting data as an alternate key.

Autonumbered surrogate primary keys make life really easy in all sorts of ways. Debatably, they improve performance too, but I have seen contrary arguments on that one.

Neil (Nobody important :-)

>
>
> --CELKO--
> Joe Celko, SQL and Database Consultant
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sun May 28 2000 - 00:00:00 CEST