"Jamie Collins" wrote in message
oups.com...
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.

I thought a PK field is indexed, no duplicates by default (and without
options to change those aspects).

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!

In this listing physical ordering is not listed directly, but at least one
of the referenced articles discusses indexing. I don't know how indexing
and physical ordering on the disk are related.


In previous discussions several people who have demonstrated a good command
of Access and of databases in general questioned the importance of clustered
indexes for physical ordering on the disk.
http://groups.google.com/group/micro...cf0d56 a19f15

http://groups.google.com/group/micro...209 cee5d14d7

There is a variety of views within those threads.

By the way, if there is a better way to reference newsgroup discussions I
would be glad to know about it.

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.

If the listings are in order of importance, then I guess you are correct.
As I have stated several times already, I finally understand (I think) a
distinction you made between PKs and indexes.

I still doubt there is a performance issue between indexing on an arbitrary
number such as EmployeeID and and arbitrary PK. Also, I may choose to list
employees by lastname, firstname, but have many occasions to order or group
by department, startdate, or what have you.
BTW, one of the articles states that the rows will be ordered according to
the primary key OR the unique index, but leaves me guessing as to what
happens if both exist.

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?

I probably did not accept the argument because I may order on one of several
fields, depending on how I wish to present the data. If one of those fields
is the unique index, but it is not the most common ordering I will use, I
wonder if there is an advantage.

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).

As far as I know, properly implemented user-level security prevents
back-door updates to the tables. I do know that there is no direct way of
opening the back end (and getting to the table data that way) without going
through the secure mdw file (although I am aware that user-level security is
not as robust as other methods available to SQL). What you described with
the Excel thing is in fact a back-door update, undertaken by somebody who is
deliberately and knowingly circumventing the intended route for entering
data. In my book that is hacking, not an innocent attempt to be more
efficient.
Users have full permissions to the table links in the FE, and limited
permissions (according to the group of which they are a member) to the
actual BE tables.
All updates are intended to happen by way of the user interface. As of now
users can still use the Shift key bypass, but there are ways to prevent
that. I haven't had the chance to implement that yet, as other matters have
my immediate attention, but plan to do so before long. As it is, unsecured
databases have been unmolested for quite some time, and very few people even
know about the Shift bypass option, so I'm not too concerned in the
immediate short term.

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.

--