Re: Surrogate keys (Was: enums)

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