Re: Artificial Primary keys

Michael Russell doodled thusly:

>start-date'. Other people's thinking in this area is that a short,
>numeric key is easier to maintain (marginally) and it will perform
>much better for the joins that are to come.
>
>Do you have any comments on the use of 'artificial' keys like this,
>rather than using the actual attributes of an assignment?
>
>For my part, I feel unsure that it's worth imposing this on the design
>without knowing that there really are maintenance/coding/performance
>advantages worth having; but I don't know -- hence this post.
>

well, here is one off the top of my hat:

You'll find that more than likely, someone will develop a screen or function that will need to change an existing employee's id and/or start date, AFTER the employee has been created and all sorts of stuff hangs off the composite PK (id+stdate).

This happens mostly because no one is perfect like a computer, and data entry errors are made. And people need to be able to change an existing id/stdate without having to delete/re-create all info for an employee. Which is what they'd need to do if we made id+stdate the PK.

This to demo that indeed surrogate keys (the "techo" term for 'artificial key') are in fact useful: they greatly simplify maintenance of data. And in some cases optimizers will perform better out of a PK made of a single column. Instead of a composite.

Of course there are cases where all this isn't needed. But in yours it looks like it would be desirable. Nothing wrong with surrogate keys: use them if they make things easier.

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Wed Jan 23 2002 - 14:37:46 CET