I beleive this is a case of one case fits all. No doubt many others wont
agree with this.
IMO the need to "MUST HAVE CONJUGATE KEYS" from a design perspective is
rare. Right now I cant think of a good reason for this, but I suppose it may
exist. In the case where this appears as the only solution to me, I find
myself rethinking the design to eliminate the Conjugate key.
I fond that there are many cases of logic that having conjugate keys
complicates the code, slows select, insert, update, or delete - one or many
of these.
I dont see any case where a surrogate key philosophy will ever get in the
road or cause performance degradation. Yes there are cases where they are
on a par, but never is conjugate better, and often surrogate is better.
Your test comparison is interesting. Have you tried deletes and inserts?
Oh, by the way, I apply the same philosophy to Interbase, MS SQL Server,
Informix, and Oracle. The argument is not reserved for Paradox.
Anyway, I think argument about this is a dead end. Better you go your way
and I go mine. The subject has been thrashed before.
Cheers,
Tony.
-----Original Message-----
From: Mike Osborne <[EMAIL PROTECTED]>
To: Multiple recipients of list database <[EMAIL PROTECTED]>
Date: Sunday, 5 September 1999 1:28 PM
Subject: Re: [DUG-DB]: how do I use paradox composite keys
I'm not sure it's quite that simple - and the surrogate key vs intelligent
key is a design decision appropriate to each situation - not sure it's a
one-size-fits-all option to recommend no composite keys.
As for specific points:
>2 reasons.
>
>1. Performance is hit.
>
Adding 500,000 records to 2 paradox tables one with composite key and one
with single integer key - no discernible difference - 1:53 and 1:52.
If you add an index to the single key table to keep it in the (composite
key) order you most often access it for grids, reporting ... then the single
key is worse at 2:30.
>2. Every time you make a query joining that table you must now refer to 2
>columns. This makes the join slower for the engine to utilise.
>
Agreed - significantly slower with multiple columns.
>3. Conjugate keys add unneccessary complexity to your code.
>
Not sure I understand why - in fact addition of an extra column and
mechanism to maintain this column must add complexity of its own.
>So why not allocate a base number to each site and offer a range of ID's
>starting at that base number OR
>
Looks like introducing a data management issue to manage number ranges. What
happens when the same range gets applied at two sites through an
administrative error and you don't find out till month end reconciliation?
(Some dumb human wrecks a great technical design.)
>if the sites all connect regularly, then why not use temporary ID's allways
>< 0 then when ever a connection to your "Home" site is made, allocate
>permenant ID's.
Isn't this adding complexity?
>What ever, making you PK an integer on a single column is seriously faster
>and easier to use. Often, the PK will carry no data information value. Its
>PRIMARY purpose is to supply uniqueness, and thus form the primary key.
>Often this technique is referred to as "Surrogate Key"
For a more eloquent discourse on this, check out
http://www.bcarter.com/intsurr1.htm .
Regards,
Mike
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
