Re: What do you do with an ENORMOUS primary key?

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:<aete2j$dck$1_at_lust.ihug.co.nz>...

> Mmmm. Thanks. Interesting that both you and Neill see a synthetic primary
> key looming over the horizon. I don't think that will work, because then
> there's no guarantee that you won't schedule a rose bush to be pruned twice
> in one day -and a poor looking specimen it would be then!! I've never liked
> synthetic keys (like Microsoft's autonumber) for just that reason: it's
> every appearance of primary-keyness, but doesn't actually guarantee
> uniqueness for your real data. But that's a philosophical discussion that
> can run and run, I think!

Surrogate keys greatly simplify the process of indexing and appropriately referencing FKs in a multi-level parent-child design. It's only after you've been through the pain of implementing and managing a 10 level hierarchy done without surrogate keys that you fully appreciate their usefulness.

I'd say Nial and I have had our fair share of that and automatically zap back to them whenever we see a potential for more than two levels of PK-FK RI.

At a higher level of complexity of RI, you'll soon realize that using a traditional approach means ending up with a bottom entity with a PK made up of 9 FKs. Not practical, particularly for index management reasons.

As I'm fond of saying: perfectrly possible, but a RPITA to manage.

There is another thread here about using stored procs instead of triggers to manage RI. You'll find as complexity increases stored procs, surrogate keys and declarative RI are the only practical way of implementing an efficient system.

>
> But thanks anyway for thinking about it.
>

No worries. Pleasure.

--
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam