Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Artificial Primary keys

Re: Artificial Primary keys

From: Nuno Souto <nsouto_at_optushome.com.au.nospam>
Date: Wed, 23 Jan 2002 13:37:46 GMT
Message-ID: <3c4ebac6.7497784@news-vip.optusnet.com.au>


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 - 07:37:46 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US