Re: Artificial Primary keys

Michael Russell doodled thusly:

>Nounu,

Nuno.

>The point about needing to change the PK is taken; don't you think
>this can be handled by cascading the changes to all the foreign keys?

many think that a PK should never be changed once created. of course, if you can afford the overhead for cascading or writing the triggers for that to happen automagically, no problem. not all dbs can do all that, though.

>Or have I misunderstood "cascading"? (As far as these "Assignments"
>are concerned, there are over-riding business rules prohibiting
>changes to PK, once their status moves from "Planned" to "Actual").

that's another way to look at it: flag as "planned", then only move to "actual" after vetting. But in the meantime, there may already be a few hundred children hanging off it. what happens to them once "actual" proves unreachable? of course, you can block attachments until status is "actual". I don't think users will buy it, though. Nope, I prefer the surrogate keys. Less hassles overall.

>
>I've no violent objection to surrogate keys; they do make me wonder if
>they result from the temptation to second-guess the server-software.
>What I mean is, perhaps we IT people bring forward some old techniques
>from flat-file design, when lack of disk-space/memory/speed meant
>tricks with data were quite accepted and indeed expected. Maybe, as
>"old" techniques, they don't apply; haven't servers been written by
>3rd/4th generation designers who know, or have inherited knowledge of,
>the business of optimisation quite thoroughly, anyway?

me neither. surrogate keys are just a convenient tool. Sure, hardware has evolved. But so has s/w and data volumes. Guess what, data volumes have evolved much faster. Whereas 20 years ago we'd store at most a few tens of thousand rows in a table or flat file or hierarchical db, nowadays rows are in multi-million counts, often times even nearing the billion mark. Sizes used to hoover around 10Mb back then, they eare multi-Tb nowadays. Even with all the optimization knowledge in the world, it's still a respectable overhead to ignore this scaling. Complexity of the apps is also much worse. It all sucks up what little we gained from the optimisation, if anything.

That means we're back to good old "go-fast" techniques, which make a lot of sense in this era of fast-CPUs/disks and lots-of-bloatware-to-sap-it-all-up.

Sorry if I sound cynical, but one thing I've noticed over the years is the extra power available to us in the new stuff has all been taken up by the extra junk crammed into the systems/apps. With the result that if you want acceptable performance when dealing with the sizes of databases of today, you better start looking at old but proven methods.

Besides, generating a surrogate key off the system itself is 1/4 of nothing in overhead. If it saves me the hassle of cascading (declarative or otherwise) in a table that has hundreds of millions of rows in it, with the subsequent adverse impact on indexes that may support PKs and FKs, I'll gladly take it. Sure it's not "pure". Neither is changing a PK.

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Thu Jan 24 2002 - 16:09:52 CET