Re: Surrogate Key vs Production Key

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<ck6qt3$gf0$1_at_titan.btinternet.com>...

Let's try again ad hope some arsehole telemarketer does not drop my telephone line again... Ah, the joys of dial-up internet!

> How does the client manage to enter thousands
> of child rows, when the error is caused by a
> mis-type ?
>

Because users are not perfect typists and auditors only do their work every once in a while. Expecting a user to always enter the correct data upfront and presume that data is correct enough to form the base of a hierarchy or series of relationships is like expecting ice cream to not melt down your fingers: looks good and tastes good, but it WILL melt.

> Deferrable Constraints should deal with this.
> And if not, then may not be a huge problem
> to produce a generic copy/delete/insert generator
> that walks the constraints to produce a program
> that does the job on auto-pilot.

I'd rather never have to do it. Particularly when I may have very complex relationships hanging off the PK.

> What about all those wonderful big systems
> that suddenly realise that they can't do partitioning
> with local primary key indexes because the
> "natural" partitioning column is not part of the primary
> key - and the users don't get any partition elimination
> in their queries because the only possible elimination on
> a meaningless key which they don't know ?

Are you saying that he only effective way of partitioning a table is through the PK? What about all those systems out there partitioned on a rolling date which has nothing to do with the PK?
And since when is partitioning dependent on PKs or vice-versa?

In fact, what I'm seeing more and more is users requesting systems that let them enter the data ANYWAY, and then allow them to go back and fix things later. I call it "fuzzy data entry". It is the bread and butter of any new designs (particularly when they are re-hashes of less stringent data entry products like Lotus Notes and such) and is now in just about any new system I get asked to do. We now have the hardware to make these things fly. But natural keys are an absolute no-no in such a situation. Received on Fri Oct 08 2004 - 22:37:26 CDT