A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Autonumber Fields



 
 
Thread Tools Display Modes
  #51  
Old September 27th, 2005, 03:39 PM
Amy Blankenship
external usenet poster
 
Posts: n/a
Default

Maybe it's cause they don't care?

-Amy

wrote in message
oups.com...

BruceM wrote:
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.'



Ads
  #52  
Old September 27th, 2005, 04:07 PM
external usenet poster
 
Posts: n/a
Default


Amy Blankenship wrote:
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.'


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.

  #54  
Old September 27th, 2005, 06:44 PM
Amy Blankenship
external usenet poster
 
Posts: n/a
Default

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

wrote in message
ups.com...

Amy Blankenship wrote:
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.'


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.



  #55  
Old September 28th, 2005, 10:14 AM
[email protected]
external usenet poster
 
Posts: n/a
Default


Amy Blankenship wrote:
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.

  #56  
Old September 28th, 2005, 03:26 PM
Amy Blankenship
external usenet poster
 
Posts: n/a
Default


wrote in message
oups.com...

Amy Blankenship wrote:
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.


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


  #57  
Old September 29th, 2005, 09:19 AM
[email protected]
external usenet poster
 
Posts: n/a
Default


Amy Blankenship wrote:
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

  #58  
Old September 29th, 2005, 12:33 PM
BruceM
external usenet poster
 
Posts: n/a
Default


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.


  #59  
Old September 29th, 2005, 01:51 PM
[email protected]
external usenet poster
 
Posts: n/a
Default


BruceM wrote:
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
dare you' faux-offended pose g).

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.

  #60  
Old September 29th, 2005, 02:14 PM
BruceM
external usenet poster
 
Posts: n/a
Default


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


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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting a table by concatenating several fields in the same table salsaguy Running & Setting Up Queries 3 March 6th, 2005 08:41 PM
Sorting a table by concatenating several fields in the same table salsaguy Running & Setting Up Queries 0 March 6th, 2005 01:33 AM
Additional fields for form based parameter query/null fields geeksdoitbetter Running & Setting Up Queries 2 January 7th, 2005 10:05 PM
Selecting Fields for Update Steve Daigler Page Layout 4 October 15th, 2004 02:13 PM
My tables lost their AutoNumber fields Bill Nicholson Database Design 2 July 2nd, 2004 02:20 AM


All times are GMT +1. The time now is 12:02 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.