Re: Surrogate keys (Was: enums)

["Jim C. Nasby" <jnasby(at)pervasive(dot)com>]

On Sat, Jan 14, 2006 at 07:28:21PM +0900, Michael Glaesemann wrote:
> 
> On Jan 13, 2006, at 21:42 , Leandro Guimar?es Faria Corcete DUTRA wrote:
> 
> >If you still declare the natural key(s) as UNIQUEs, you have just made
> >performance worse.  Now there are two keys to be checked on UPDATEs  
> >and
> >INSERTs, two indexes to be updated, and probably a SEQUENCE too.
> 
> For UPDATEs and INSERTs, the "proper" primary key also needs to be  
> checked, but keys are used for more than just checking uniqueness:  
> they're also often used in JOINs. Joining against a single integer  
> I'd think it quite a different proposition (I'd think faster in terms  
> of performance) than joining against, say, a text column or a  
> composite key.

a) the optimizer does a really poor job on multi-column index statistics
b) If each parent record will have many children, the space savings from
using a surrogate key can be quite large
c) depending on how you view things, putting actual keys all over the
place is denormalized

Generally, I just use surrogate keys for everything unless performance
dictates something else.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461