Re: Separate PK in Jxn Tbl?

> OK since you admit you raised the 'budget' issue, what do *you* think
> the impact on 'budget' is when choosing random autonumber over
> incremental autonumber?

If you strictly take a look at performance considerations, random autonumbers don't scale well with indexes. Most (but not all) systems will probably scale better if you are using a monotonously increasing primary key. A monotonously increasing sequence is also probably easier to read than a set of random number when you are debugging a system. The fact that I'm not interested in the value of any particular primary key doesn't mean that I won't mind to see a collection of primary key values to have the property to be a sequence of monotonously increasing (or decreasing, especially in the case of some replication scenarios) numbers. Like I said in my previous post, my brain is like the budget of my clients: it's not infinite either.

However, there are of course some scenarios where a random autonumber will be used. This is often used in replicated systems; particularly when there are online/offline clients.

> Again, I ask you to consider the posts we see in the Access groups
> asking to reseed an incremental autonumber back to one or because they
> are perplexed/outraged that gaps have appeared in their autonumber
> sequences. If the exact values of these so-called surrogates have
> "zero importance" to these people then why are they making these
> requests?

You don't see this only in Access groups. The same question is often asked on other groups as well: searching Google Groups for IDENTITY_INSERT reveals 6520 hits:

http://groups.google.com/groups/search?ie=UTF-8&oe=UTF-8&q=IDENTITY_INSERT

> PS what about the other comments you seemingly aimed at me: codifying,
> reengineering, etc? Why no mention of them in your reply?

I seemingly aimed no comment at you. (And usually - but not always - at anyone else.). This is a thread with a great number of messages posted by multiple peoples and my comments are destined to anyone interested in reading my posts. Maybe my style of writing is not enough impersonal but practically all my posts should be read as being impersonal; excerpt for the inclusion of the usual forms of politeness.

As for why I don't mention everything in my replies, I volontairily do so because I try to reply only when I've something new to say. When I wrote something, if somebody else is not able to understand it - without making any assumption here on why he/her don't understand it - quite probably that he/her won't be able to understand it again if I repeat myself. Usually, I don't feel the need to try to have the last word. There are a lot of people that will read these messages and I think that they are quite capable of forging their own opinion even if I didn't repeated the same thing over and over again or didn't posted last. Of course, my mind is not perfect; so I do repeat myself or make a last post from time to time.

-- 
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Jamie Collins" <jamiecollins_at_xsmail.com> wrote in message 
news:943c66aa-3c20-4e6f-9b64-a91c61a160aa_at_i72g2000hsd.googlegroups.com...


> On Jan 29, 8:37 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the

> blanks, no spam please)> wrote:

>> I was making a direct reference to the following quote:


>>

>> > In a recent thread on this subject, Tony Toews Access MVP qualified

>> > that he liked using incremental autonumbers (rather than random)

>> > because they where easier to type (WHERE ID = -2001736589 may

>> > encourage typos) and easier to drop into conversation ("Hello Tony?

>> > I'm seeing a problem with the record where the ID is -2001736589...").

>>

>> Of course, theoritically and in a world with unlimited budget, you're 

>> right

>> in the sense that a surrogate key should never cross the boundaries of a

>> database (the interface beeing located inside in these boundaries) but my

>> clients don't have infinite budget and my brain isn't infinite either.

>

> OK since you admit you raised the 'budget' issue, what do *you* think

> the impact on 'budget' is when choosing random autonumber over

> incremental autonumber?

>

> My understanding is that Tony Toews Access MVP chooses incremental

> autonumber because he likes to refer to a row (entity) using an

> address (or possibly by position) i.e. he doesn't choose random

> autonumber because he doesn't like the values it would generate (too

> many digits, negative values, etc). I offer this to discredit your

> assertion, "for those who are using surrogate keys, the exact value of

> an address inside the database has zero importance".

>

> Again, I ask you to consider the posts we see in the Access groups

> asking to reseed an incremental autonumber back to one or because they

> are perplexed/outraged that gaps have appeared in their autonumber

> sequences. If the exact values of these so-called surrogates have

> "zero importance" to these people then why are they making these

> requests?

>

> PS what about the other comments you seemingly aimed at me: codifying,

> reengineering, etc? Why no mention of them in your reply?

>

> Jamie.

>

> --

>