On 23 Aug, 16:33, "BruceM" wrote:
I have been saying the same thing over and over, and will stop doing so
after this message. I do not rely on autonumber to make a record unique.

I've heard you say this over and over and I believe you. Good for you.
Neither do I. I'm glad we agree. Actually, I think we agree on most
things. BTW no sarcasm intended.

You have not answered my objection to linking tables through fields that are
subject to change.

I've not tried to *refute* your objection because I think you have
every right to hold that view. I cannot tell you whether joining your
tables on fields that are subject to change is good or bad or
indifferent because I don't know anything about their structure or
content (but, sight unseen, my money would be on 'indifferent').

However, I feel I have *answered* your objection e.g. on 16 August in
this thread when I said, "I think the SQL DBMS should store the key
value only once and use an internal surrogate." We both agree that
physically storing the key value in all the referencing tables is a
Bad Thing but we have different responses to it. My response is to
shrug: it's a 'feature' of the product and if I thought the product's
performance, CASCADE referential actions, etc were not up to the job
then the product is not fit for purpose and it's a free market. Your
response is to roll your own surrogate using autonumber. I don't feel
I've tried to push my approach down your throat though I have
suggested you to consider the effects of placing a *physical* index on
an autonumber column and given some examples of how a sole autonumber
PK for referencing in FKs can work against some design patterns such
as subclassing. I've acknowledged that your way valid but is not for
me, primarily because it makes referencing tables' data harder to
read, but have wished you luck with yours.

In the same spirit, may I press you further for detail of,
"Knowledgeable people [who] have pointed out that there are many
performance factors to be considered before physical ordering on the
disk," please? In my experience, most supposedly knowledgeable people
give it no consideration; others don't seem to believe that clustering
actually happens in Jet at all!

I think compacting is a good
thing, and I have observed performance differences after compacting.
However, I have seen information about how indexing choices can degrade
performance (for instance, when using indexes on City and PostalCode
fields). As the article points out, there is no single correct answer about
indexing.

Again, we agree there are many factors to performance. You said you'd
heard physical ordering was not at the top of some lists. I've shown
two from Microsoft where it is.

One thing I'm still not clear on. You said earlier, "I don't accept
the physical ordering argument." I thought you meant you did not see
how it can be relevant to either choice of PK or performance
implications. Did I misunderstand what you meant? Has anything I've
presented caused you to revise this earlier held view? I get how you
choose your PKs (unchanging field values, single column FKs) but do
you get how physical ordering might affect PK choice for someone else?

Then why give you give them (via their groups) the permissions to do
so?

I don't. I never said I did.

It seems I'm not understanding your approach. I see two basic
approaches:

1) Users can perform updates to your tables' data via your application
because you have granted the privileges on the tables to the users
(but does not preclude the same users from performing updates to your
tables' data via another route).

2) You have not granted the privileges on the tables to the users but
they can still perform updates to your tables' data via your
application because you have granted privileges on the application to
the users and granted privileges on the tables to your application
(the advantage here is that they can *only* perform updates via your
application).

Is your approach one of these, a variation or something completely
different? Note a common third approach is that everyone runs as an
administrator but that would not seem to apply to you.

Jamie.

--