Autonumber Fields

[peregenem]

I am not going to use a
multi-field PK.

Why? You seem to be basing this on 'gut feeling'. Remember PK =
clustered index (physical order), and shouldn't be confused with a
simply NOT NULL UNIQUE candidate key.

A name and address combination
is a poor choice for PK.

Remember PK = clustered index. If you have relevant queries which use
these columns then they stand a good chance of making an excellent PK.

Updating many records if somebody's address
changes makes no sense whatever. It destroys the whole point of using a
relational database.

No, it tells you that using an name + address doesn't make a very good
key (as you said earlier) but if it's all that you've got then you are
stuck with it (or change the system). You seem to be assuming a key
cannot change, again based on gut feeling alone. I ask again, why do
you think ON UPDATE CASCADE was invented?

 

[peregenem]

Fred Brown, xxx D Street, Parma, Idaho
Fred Brown, xxx D Street, Parma, Idaho

How on earth do you tell them apart!? You'd better issue your friends
with ID numbers and start calling them "Fred Brown 1" and "Fred Brown
2". Or do you have some sort of advanced biometrics testing or visual
recognition system going on there? Do they ever try and pretend to be
the other one, and really mess up the data you are collecting about
them?

This week, I have spoken to two organizations - supermarket home
delivery service and bank respectively - on the phone without my
customer reference to hand and they used my 'name' + 'address' + 'has
an account with us' as a key i.e. a way of identifying me. Thankfully,
the latter had some additional security information I had to supply but
the former were just happy to get a truck full of melting ice cream off
their hands.

 

[peregenem]

I am inclined to use autonumber or some other fixed value (e.g. Invoice
Number) as the PK.

Word of warning: an incrementing autonumber can leave small gaps in a
sequence e.g. a autonumber value was served out but the transaction was
rolled back and the served number is never reused. I've heard said that
if exposed, e.g. as an invoice number, you may have to account for the
missing numbers to an auditor. I know from a previous career that an
auditor will ask about missing numbers in a sequence of cheque/check
numbers. The only 'orders' database application I've worked on was
'life critical', hence we used no autonumbers and all data had to be
retained for audit trail purposes, even on rollback.

 

[BruceM]

I use some sort of Dmax +1 code to assign the number when I need sequential
numbering. I had intended by the use of "or" to convey that invoice number
is not autonumber.

 

[peregenem]

I had intended by the use of "or" to convey that invoice number
is not autonumber.

I totally read that wrong ;-)

 

[BruceM]

To clarify, I am not going to use a multi-field PK when there are other (and
simpler) means to guarantee uniqueness. If I have PartNumber and Process
(plating, welding, etc.) it makes sense to combine the two, since the
combination of Blade and Plating should appear just once. Combining
FirstName, MI, LastName, and a few address fields PLUS an autonumber or some
other kind of ID, then storing all of those fields in the Child table, makes
no sense to me. I can't believe that it is more efficient storing so much
redundant data then to have the index be based on an artificial number. It
has a lot to do with reading what others have written here (people who offer
a lot pragmatic and practical advice, and who have demonstrated again and
again their command of the program). EmployeeID (a four-digit number) works
quite well. I have no intention of bloating my database by storing several
other (and unnecessary) fields in the interest of a physical order that is
as arbitrary as any other way of arranging the data. Sometimes I need to
arrange Employee information by criteria other than LastName (by Department,
for instance, or by date). Even if I cared about physical order, one way of
ordering the records in my table is as good as another when I need to
arrange them in so many different ways in the course of using the database.
EmployeeID is as good a choice as any. Same with an autonumber.
"Destroys" is too strong a word. However, you will not be able to convince
me that continually updating multiple records and storing so much redundant
data improves anything in a situation where the unique ID number is part of
the record anyhow.
You will need to look elsewhere for a convert. Your thoughts have been
interesting, and have prompted me to investigate some new areas, but are not
going to inspire me to rework my basic approach of basing my PK on
uniqueness rather than on a particular physical order.

 

[BruceM]

Interesting article. I'll check out the site when I have more time. MSDE
may well be useful at some point, but I do not have the extra time to look
into it right now. The reality of my world is that I have a certain number
of things I need to accomplish and a limited amount of time in which to do
so. SQL server in any form is not on my horizon.

 

[BruceM]

An ID can be arbitrary, sequential, based on a combination of date and
sequential number or letter and sequential number, etc. Where I work
EmployeeID is assigned sequentially, and is not seen except when adding or
editing an employee record. If two people have the same name it does not
help to distinguish them as John Doe 4421 and John Doe 4241. If with about
100 employee records I confront that kind of duplication I will need to come
up with something "real" to distinguish them (MI, Department, Title, or
whatever) in the real world. For the purposes of the database, ID number is
fine. It is a compact and efficient PK and FK.

 

[peregenem]

To clarify, I am not going to use a multi-field PK when there are other (and
simpler) means to guarantee uniqueness. If I have PartNumber and Process
(plating, welding, etc.) it makes sense to combine the two, since the
combination of Blade and Plating should appear just once.

I'll try one last attempt at getting the message across. Use your
multi-field PK to build the clustered index. The clustered index is
for that table and that table alone. Use your simpler 'PartNumber and
Process' in the FOREIGN KEY relationship. If it isn't already,
constrain 'PartNumber and Process' with NOT NULL UNIQUE. Remember that
you can have many NOT NULL UNIQUE constraints in a table but only one
clustered index (=PK)

It has a lot to do with reading what others have written here (people who
offer a lot pragmatic and practical advice, and who have demonstrated
again and again their command of the program)

Ask yourself: do these other people recommend an autonumber as PRIMARY
KEY in the knowledge that it creates a clustered index (physical
ordering)? I've yet to hear anyone here say, 'Yes, autonumber makes for
a fine clustered index.'

 

[peregenem]

autonumbers are Long Integer, not Integer

Have you heard of the expression, "Win win"?

I use the uppercase INTEGER when referring to the Jet data type (see
http://office.microsoft.com/en-us/assistance/HP010322291033.aspx) which
coincides nicely with the ANSI data type.

It's not about losing. Open you mind and you may learn something.

 

[Amy Blankenship]

Maybe it's cause they don't care?

-Amy

I'll try one last attempt at getting the message across. Use your
multi-field PK to build the clustered index. The clustered index is
for that table and that table alone. Use your simpler 'PartNumber and
Process' in the FOREIGN KEY relationship. If it isn't already,
constrain 'PartNumber and Process' with NOT NULL UNIQUE. Remember that
you can have many NOT NULL UNIQUE constraints in a table but only one
clustered index (=PK)


Ask yourself: do these other people recommend an autonumber as PRIMARY
KEY in the knowledge that it creates a clustered index (physical
ordering)? I've yet to hear anyone here say, 'Yes, autonumber makes for
a fine clustered index.'

 

[peregenem]

Maybe it's cause they don't care?

You don't care that there is a difference between NOT NULL UNIQUE and
PRIMARY KEY?

You don't care to hear something that challenges you conceptions?

You don't care that you may give advice that inadvertently falls short
of the best advice?

I think there is a 'care of duty' to give the best advice one possibly
can in these groups.

 

[John Vinson]

How on earth do you tell them apart!?

By using other data which you did not include in your candidate key
("Young Fred" and "Old Fred", or "Fred Junior" and "Fred").

John W. Vinson[MVP]

 

[Amy Blankenship]

Wow. Take a valium. I think everyone following this thread has absorbed
your point, but you can't make everyone agree with you. That's why we have
Democrats and Republicans...because two people can look at the same set of
facts and reach different conclusions based on them. That doesn't make
either side stupid or ignorant. It just means they see things in different
ways.

Why do you care if I care?

-Amy

 

[peregenem]

Wow. Take a valium. I think everyone following this thread has absorbed
your point, but you can't make everyone agree with you. That's why we have
Democrats and Republicans...because two people can look at the same set of
facts and reach different conclusions based on them. That doesn't make
either side stupid or ignorant. It just means they see things in different
ways.

Why do you care if I care?

Call me a caring person <g>. It bothers me that people who are
*rightly* considered experts in their field make misstatements which
are taken as 'gospel'.

This is all a bit 'emperor's new clothes' for me as it is. I don't
think prescribing me hypnotics will help, thanks Doctor. "Democrats and
Republicans"? This is a global community, so could you provide a global
analogy, please? In your country, are political commentators considered
sick and/or in need of drugs when they call on politicians to review
their policies when new evidence comes to light?

I think we should be constantly reviewing our advice to ensure it is
the best it can be, especially when we are propagating ideas in public
forum. I'm thinking of the common good, the best for everyone. I've
nothing to gain personally from this; my MVP status surely having been
secured already <vbg>.

What you are currently using PRIMARY KEY to mean you could replace with
NOT NULL UNIQUE constraints, then you would be free to use PRIMARY KEY
for something more appropriate. PRIMARY KEY may mean other things in
other SQL products but in Access/Jet it means 'super unique index'. Is
it good advice to recommend using your one 'super unique index' for the
table on a single incrementing INTEGER column?

I think the professional response would be, "Clustered index? I didn't
realize that. EITHER I will now alter the advice I give in future. OR
However, after due consideration, I have decided not to alter the
advise I give in future because | I think a monotonic INTEGER makes an
excellent clustered index. | I would be giving different advice from
that of my peers (I've got a reputation to protect). | <some other
considered reason>.

I don't think "I don't care" is a professional response. I genuinely
respect and thank you for your honesty, though.

 

[Amy Blankenship]

Call me a caring person <g>. It bothers me that people who are
*rightly* considered experts in their field make misstatements which
are taken as 'gospel'.

This is all a bit 'emperor's new clothes' for me as it is. I don't
think prescribing me hypnotics will help, thanks Doctor. "Democrats and
Republicans"? This is a global community, so could you provide a global
analogy, please? In your country, are political commentators considered
sick and/or in need of drugs when they call on politicians to review
their policies when new evidence comes to light?

I think we should be constantly reviewing our advice to ensure it is
the best it can be, especially when we are propagating ideas in public
forum. I'm thinking of the common good, the best for everyone. I've
nothing to gain personally from this; my MVP status surely having been
secured already <vbg>.

What you are currently using PRIMARY KEY to mean you could replace with
NOT NULL UNIQUE constraints, then you would be free to use PRIMARY KEY
for something more appropriate. PRIMARY KEY may mean other things in
other SQL products but in Access/Jet it means 'super unique index'. Is
it good advice to recommend using your one 'super unique index' for the
table on a single incrementing INTEGER column?

I think the professional response would be, "Clustered index? I didn't
realize that. EITHER I will now alter the advice I give in future. OR
However, after due consideration, I have decided not to alter the
advise I give in future because | I think a monotonic INTEGER makes an
excellent clustered index. | I would be giving different advice from
that of my peers (I've got a reputation to protect). | <some other
considered reason>.

I don't think "I don't care" is a professional response. I genuinely
respect and thank you for your honesty, though.

How bout "after due consideration, I decided that whether or not it is a
clustered index is completely irrelevant to the applications I develop and
to most people who post to these groups, who only want to get the job done,
so I decided I don't care." The simple fact is that whether or not a
primary key creates a clustered index DOES NOT MATTER on a practical level
to most developers, as other considerations take priority
http://www.dbpd.com/vault/9805xtra.htm.

I'd like to point out that while I am a Team Macromedia Member, I am not an
MVP at this time. I just happen to be obsessive about answering questions.
One thing I have learned after many, many years of giving advice on forums
is that it really doesn't matter much how accurate or inaccurate advice is,
as users will accept the advice that is most in line with what they believe
they can do and also most in line with what they *thought* the solution was
going to be. By the same token, the fact that I am not as expert as I am
going to be next month or next year (or some other person is now) should not
prevent me from offering what help I can, because what help I do offer *is*
actually helping people.

I doubt *anyone* is currently being helped by your insistence that everyone
should care about clustered indexes as passionately as you do. It's a dead
horse. Find something more productive to beat.

-Amy

 

[peregenem]

The simple fact is that whether or not a
primary key creates a clustered index DOES NOT MATTER on a practical level
to most developers, as other considerations take priority
http://www.dbpd.com/vault/9805xtra.htm.

Your link suggests you still haven't understood my point (my fault, I
fear). I am not saying, don't use an autonumber as a surrogate. I am
saying, don't squander you one clustered index on a single INTEGER
column that's (a) pretty much guaranteed to be unique and not null by
system (autonumber) anyhow and (b) easily constrained using explicit
NOT NULL UNIQUE to be doubly sure.

Question: do you use indexes specifically for performance purposes? (I
mean as distinct from, say, a unique index for data integrity reasons.)

If you don't, then I can see why my point keeps flying over your head.
Fair enough, you wouldn't be interested and I'll move on another day to
someone who is.

If you do use indexes specifically for performance purposes then it
makes no good sense to disregard clustered indexes. I am not able to
reconcile "I choose indexes to enhance performance" with "I choose to
ignore clustered indexes".

One thing I have learned after many, many years of giving advice on forums
is that it really doesn't matter much how accurate or inaccurate advice is,
as users will accept the advice that is most in line with what they believe
they can do and also most in line with what they *thought* the solution was
going to be.

I have to say, that one made me cringe. I'm reminded of this old
chestnut:

<quote>

The analog I used is that if this were a woodcraft group, they post

"What is the best kind of rocks to smash screws into fine furniture?"

You answer "Your whole approach is wrong. You need to learn about
screwdrivers and starting holes. Putting soap on a screw can keep the
bosard from splitting. Etc. "

They reply: "Screw you! I just want an answer. I want it NOW, NOW,
NOW! I don't care that this desk is going to fall apart if I build it
this way. And how dare anyone with a few more decades of experience
and education in this field try to tell me anything!"

Then someone tells them: "Granite. Big huge chunks of granite will
smash screws in real good!"

And finally the arrogant newbie replies; "See? They helped me!" And
they proceed under the idea that anyone can be a master craftsman, as
long as they have a pile of five pound granite rocks at hand.

</quote>

 

[BruceM]

The analog I used is that if this were a woodcraft group, they post

"What is the best kind of rocks to smash screws into fine furniture?"

You answer "Your whole approach is wrong. You need to learn about
screwdrivers and starting holes. Putting soap on a screw can keep the
bosard from splitting. Etc. "

They reply: "Screw you! I just want an answer. I want it NOW, NOW,
NOW! I don't care that this desk is going to fall apart if I build it
this way.

A crude analogy (I assume you meant "analogy"). Databases don't fall apart
for want of a "natural" key. Maybe in some cases they don't perform as
well. More likely beginner problems are going to be related to things such
as storing data redundantly. When you talk about clustered indexes and
physical order, and then you include code that includes MAKE TABLE without
explaining what do do with the code, the person who is trying to figure out
how to manage a few hundred records is left with no clear idea of how to
implement your suggestions. When somebody else offers a practical
suggestion, which the beginner tries and with which he or she has success,
that person is apt to implement the method that works.
In another (and I hope more apt) analogy of my own, I think that any serious
user of Microsoft Word should learn about styles (as the term is used in
Word). But even a person who uses Word extensively will occasionally resort
to direct formatting. For the person who uses Word for an occasional letter
or something of the sort, I would probably just show that person how to
apply direct formatting. I would mention styles, and encourage their use,
but not insist on it. Their documents will be just fine.

Then someone tells them: "Granite. Big huge chunks of granite will smash
screws in real good!"

Have you caught on that experienced and thoughtful people disagree with you?
You yourself disparage the education and experience of others when it runs
contrary to your preferences.

And finally the arrogant newbie replies; "See? They helped me!" And
they proceed under the idea that anyone can be a master craftsman, as
long as they have a pile of five pound granite rocks at hand.

"Arrogant newbie", huh? That's pretty contemptuous. I doubt beginners see
themselves as database experts. They have a problem to solve, receive
suggestions, implement those suggestions, build a functional database,
please their bosses, and move on to the many other things they doubtless
need to do. Does this make a person arrogant?

</quote>

Consider plain text message format for newsgroup postings.

 

[peregenem]

A crude analogy (I assume you meant "analogy").

Note 'Analog' (alternatively spelling 'analogue') is the root of
'analogy', however other typos suggest you are correct.

"Arrogant newbie", huh? That's pretty contemptuous.

Sure is The <<quote>> <</quote>> thing was supposed to be plaintext
i.e. to tell you I was quoting someone else. Sorry if the point was
lost but those words are not my own (excuse me while I adopt a 'How

Have you caught on that experienced and thoughtful people disagree with you?

Have you caught on that, because I have a 'broad' understanding of the
issue, I can debate the 'autonumber makes a fine PK' from both sides of
the fence? This is a good learning technique (devil's advocate) and
ensures you don't hold irrational biases. Well informed biases are a
different matter and something I have respect for. So allow me to take
exception to this of yours:

You yourself disparage the education and experience of others when it runs
contrary to your preferences.

Show me where I have disparaged 'education and experience' for holding
an informed opinion contrary to mine and I'll issue an overdue apology.
(You know I'm really not Joe Celko, don't you <g>?)

Did you ever try to test a 'point of view' to see if it would stand up?
This is the basis of scientific testing (Popper's falsification): you
don't test your hypothesis ('All swans are white') directly; rather you
test your null hypothesis ('Some swans are not white') and if the null
hypothesis can be proven then you must reject your hypothesis.

I think that Amy cannot yet put up an effective case for 'This House
believes autonumber don't make great PKs', because see can't see it
from 'my' side, and therefore is in danger of holding an irrational
bias. Reminds me of another star of the philosophy of science, Kuhn's
paradigm shifts:

(Quote) Revolutions are to be sought on Popper's view also, but not
because they add to positive knowledge of the truth of theories but
because they add to the negative knowledge that the relevant theories
are false. Kuhn rejected both the traditional and Popperian views in
this regard. He claims that normal science can succeed in making
progress only if there is a strong commitment by the relevant
scientific community to their shared theoretical beliefs, values,
instruments and techniques, and even metaphysics.
(Unquote)
[http://plato.stanford.edu/entries/thomas-kuhn/]

My take on Kuhn: it would take a revolution to wean exponents from
their 'autonumber as PK' habit because people like Amy have their own
agendas (e.g. to fit into the MVP crowd) and 'don't care' about
advancement for the common good.

BTW I don't mean to be harsh on Amy; I admire her for being honest and
vocal where many fall silent. And there's nothing wrong with wanting to
be an MVP, just don't lose your voice ;-)

And BruceM, you are the most fun person round these parts. You listen
with an open mind, you respond and you have a voice. Big respect to
you, too.

 

[BruceM]

Show me where I have disparaged 'education and experience' for holding
an informed opinion contrary to mine and I'll issue an overdue apology.
(You know I'm really not Joe Celko, don't you <g>?)

Maybe "disparage" was the wrong word, but "You don't understand what PK
means" (and words to that effect) is probably not an effective way of trying
to win an experienced Access person to your viewpoint. Tends to come across
as "You don't know what you're talking about".

Did you ever try to test a 'point of view' to see if it would stand up?
Yes.

This is the basis of scientific testing (Popper's falsification): you
don't test your hypothesis ('All swans are white') directly; rather you
test your null hypothesis ('Some swans are not white') and if the null
hypothesis can be proven then you must reject your hypothesis.

I'm not familiar with Popper, but I recognize the logic. Trouble is, it
seems to me, there is a subjective element when the discussion turns to
which of two systems is better. Faster (and maybe More Efficient) are
testable, but may not be significant in the real world. In the absence of
detectable (by humans) differences between two systems, "faster" becomes
rather abstract. In the computer hardware world, one type of architecture
or BIOS configuration or whatever may be faster, but with a performance
difference that is undetectable for most purposes. Since I don't do video
editing or Auto CAD or processor-intensive things like that, I don't care.
I won't bother to upgrade or reconfigure for my purposes.

And BruceM, you are the most fun person round these parts. You listen
with an open mind, you respond and you have a voice. Big respect to
you, too.

You are an interesting character, I'll say that for you. Thank you for your
kind words. Peace.