How do you mulitply in a field?

On 16 Aug, 17:58, "BruceM" wrote:
Regarding autonumber or other arbitrary PKs

Wait up. PRIMARY KEY designation is *always* arbitrary but I think you
meant 'artificial key' (or similar). Do you mean keys in the logical
model or in the physical implementation...?

understand
that I *never* argued in favor or creating a table for which the "only
unique constraint is defined on a sole autonumber column".

Do I not recall correctly a thread where you had an entity type
'trainer' (natural person) where there was no industry standard
identifier and the compound of the available attributes (company
identifier, person full name) did not yield uniqueness so you omitted
a key from your logical model and used an autonumber PRIMARY KEY in
your Access implementation? Apologies in advance if I've recalled
incorrectly (google groups archive search seem to be broken just now).

Also, note that I was addressing the group rather than you personally;
again, sorry if I was not clear.

If I have a listing of
vendors, what is the *unchanging* unique constraint?

In the logical model, a good key should be *stable*; immutability is
the ideal since but real life is not always ideal.

I too wish all your vendors were issued with an unchanging identifier.
In which sector do to you operate e.g. DUNS number? In mine,
incorporated bodies must be registered with a government agency which
issues a public and unchanging (for all practical purposes)
identifier. Although there are incentives for businesses to
incorporate, I still need to model unincorporated businesses;
fortunately, we have "passing off" laws which makes trading name (yes,
can change) plus trading period a reasonably stable key. I also need
to model businesses from all other jurisdictions for which an
enterprise key of our own design is used.

I've found trusted sources of potentially useful identifiers wary of
revealing details (I found the 'DX Exchange' people in the UK most
unhelpful).

Yes, there are no easy answers, autonumber included.

My main problem with autonumbers is that they do not exist in the
logical model, therefore they are not a logical key. This was the
problem I recalled from your 'trainers' scenario.

A record's
uniqueness is one thing. The means of identifying it in relationships is
another.

In the physical implementation I've no problem with you or anyone
using a so-called surrogate if you also have a natural key or
enterprise key which exists external to the database. I wouldn't
recommend doing it myself for a variety of reasons e.g. doing so makes
data harder to read.

maybe cascading updates of multi-field keys are not a problem. I would
rather avoid them

Ideally (that word again), I think the SQL DBMS should store the key
value only once and use an internal surrogate, in the way you do by
hand with autonumber, to point to it; this way an ON UPDATE CASCADE
action would physically change only one value. Such SQLs exist but
professionally I need use Microsoft's products, at least in the
immediate future; Microsoft doesn't have them so I go without.

So my ideal surrogate would be hidden and I'd still see the real key
values in the referencing tables but that's not the reality for me.
With your way (by hand with autonumber) you either have to work with
the meaningless values or use a JOIN (or three or thirteen) to see the
real values, so it's not for me thanks and good luck to you.

You will not change my mind on the subject, nor I yours.

One thing that tickles me about the 'autonumber PK' advocates it that
they most often use the incremental Long Integer flavour, presumably
because it makes the data easier to read! If I cannot dissuade you
from autonumber, I urge you to choose random: it will improve
concurrency (let's not start on the whole 'physical clustering on
disk' thing), you will be less inclined to expose the autonumber of
users and, having to type the values over and over, you may come to
appreciate a well designed key (fixed width, check digit, etc). It's
'cruel to be kind'; making you type replication IDs (GUID) would just
be cruel g.

Jamie.

--