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
