Surrogate And User Defined Keys

[wackoyacky]

Hi gurus!

I would just like to ask whether what are your preferences in choosing primary keys of the tables, is it by using surrogate keys which normally using an identity field that auto increments, or your using user defined keys which is a bit more tricky and lot of coding required to produce but is more controllable?

Any inputs would be greatly appreciated.

Thanks,
Enzo

[russell]

depends on how the data is used. also depends on how many fields are in the natural key. and a big difference -- do foreign keys need to point back to this table? if you want other tables to be FK'd back to the columns that make up the natural key, then the choice is already made for you -- put PK over those columns.

if your dbms is sql server, there is no reason necessarily to put your clustered index over your primary key. can cluster on identity field, yet still use natural key for PK -- thus allowing FKs to point at them.

now if you are constantly passing the values of the key around to a front end application, then an identity column almost always makes sense. it also creates a MUCH smaller footprint for your indexes, which will be a nice performance improvement.

while it is not always the best practice, it is usually the best to create an idenetity field as a surrogate PK, or at the very least as your clustered index field when speaking of performance.

[wackoyacky]

I'm using SQL Server.

When I usually create table that doesn't need any key to be shown in the UI, I always make use of the Identity field as the primary key. And this is referenced by other tables as FK. Is this ok? Because I remember sometime back, somebody told me that identity fields are not good to use.

Actually when I was saying user defined keys, I'm not pertaining in creating a composite key, but instead still a single field something like AAA001. Because right now I have a dilemma for the tables that I will be creating that needs the primary key showed in the UI. So here's my two option:

1.) Create a user defined key(which will be shown in the UI), set it as unique index, then have an identity field as the primary key and this identity field will be the FK for the other tables.
or
2.) Create a user defined key(which will be shown in the UI), set it as the primary key and also this field will be the FK for the other tables.

I was thinking which way is the best. I have never dealt with any enterprise application database so I don't know which setup is effective & efficient especially if you have million of records.

Thanks.

[russell]

i like option 1, and performance will be better that way too.