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  

What are AutoNumber Dangers



 
 
Thread Tools Display Modes
  #1  
Old April 28th, 2010, 09:11 PM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default What are AutoNumber Dangers

I recently viewed a post that said "autonumber is fine as long as you dont
display it to the user..." Everything in my database references
autonumbers!! did i make a big mistake somewhere?? i am even marking parts
with ID numbers that are generated by the autonumber field on a few of my
tables...

Should i be worried??

Do i need to seriously look into a new approach??

any comments are greatly appreciated..

Barry
  #2  
Old April 28th, 2010, 10:01 PM posted to microsoft.public.access.tablesdbdesign
Daniel Pineault
external usenet poster
 
Posts: 658
Default What are AutoNumber Dangers

There is nothing wrong with autonumbers, even in more advanced dbs dbas
create a form of autonumber (they call it differently but it serves the same
purpose).

The problem is that many people mistakenly try to rely on autonumbers as,
for instance, a document number. It is merely a unique id associated to a
record. In reality it should serve no other purpose. The end user should
even be aware of autonumber. If you need to number your records and display
it to the user, ie a sequential number associated to a document, then create
a custom field in your table for this distinct purpose.

Short answer, there is nothing wrong with autonumber, but rather the way
some people mistakenly try an use them.

Don't worry!
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Barry A&P" wrote:

I recently viewed a post that said "autonumber is fine as long as you dont
display it to the user..." Everything in my database references
autonumbers!! did i make a big mistake somewhere?? i am even marking parts
with ID numbers that are generated by the autonumber field on a few of my
tables...

Should i be worried??

Do i need to seriously look into a new approach??

any comments are greatly appreciated..

Barry

  #3  
Old April 28th, 2010, 10:13 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default What are AutoNumber Dangers

Same as what Daniel said...couple more notes.

When it's said to never show it to the outside world, that's to guard
against anybody ever trying to change one. The minute you try to use them
for a real world use, you run the risk of somebody with more authority than
brains or listening skills dictating a change or reassignment of a number
which violates an important and essential database principle.
  #4  
Old April 28th, 2010, 10:37 PM posted to microsoft.public.access.tablesdbdesign
Rick Brandt
external usenet poster
 
Posts: 4,354
Default What are AutoNumber Dangers

Barry A&P wrote:

I recently viewed a post that said "autonumber is fine as long as you dont
display it to the user..." Everything in my database references
autonumbers!! did i make a big mistake somewhere?? i am even marking
parts with ID numbers that are generated by the autonumber field on a few
of my tables...

Should i be worried??

Do i need to seriously look into a new approach??

any comments are greatly appreciated..


In my opinion "never expose AutoNumbers to users" is vastly overstated. It
is based on an assumption that users will make assumptions that might
confuse them (like the values should not have gaps).

Well, amongst users the stupid ones will make assumptions that confuse them
about LOTS of things. The smart ones not so much. Many situations will
never expose a long continuous block of records to a user at one time where
an assumption about gaps comes into play anyway.

There are cases where the "business rules" dictate no gaps. In those cases
avoid AutoNumbers. There is little reason to avoid them (and show them to
users) when business rules don't care.

  #5  
Old April 28th, 2010, 10:59 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default What are AutoNumber Dangers

Barry

What Daniel and Fred said ...

Access Autonumbers are designed to serve as unique row identifiers, nothing
else. As such, they are rarely suitable for human consumption.

Keep using them as unique row identifiers, but don't expect your users to
understand ... OR CARE!

If you want your users to care about some number, use Daniel's approach and
create a new field, in addition to your autonumber field. Use that new
field to hold a "sequence number". That one's OK to show the users...

(and all things in moderation... a fellow whose experience I greatly respect
quite often displays autonumbers to his user ... but he has a very high
class of users ...g)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Barry A&P" wrote in message
...
I recently viewed a post that said "autonumber is fine as long as you dont
display it to the user..." Everything in my database references
autonumbers!! did i make a big mistake somewhere?? i am even marking
parts
with ID numbers that are generated by the autonumber field on a few of my
tables...

Should i be worried??

Do i need to seriously look into a new approach??

any comments are greatly appreciated..

Barry



  #6  
Old April 29th, 2010, 12:13 AM posted to microsoft.public.access.tablesdbdesign
Armen Stein[_2_]
external usenet poster
 
Posts: 157
Default What are AutoNumber Dangers

On Wed, 28 Apr 2010 14:59:31 -0700, "Jeff Boyce"
wrote:

What Daniel and Fred said ...


Me too!

Access Autonumbers are designed to serve as unique row identifiers, nothing
else. As such, they are rarely suitable for human consumption.


Rarely, but not never.

If you want your users to care about some number, use Daniel's approach and
create a new field, in addition to your autonumber field. Use that new
field to hold a "sequence number". That one's OK to show the users...

(and all things in moderation... a fellow whose experience I greatly respect
quite often displays autonumbers to his user ... but he has a very high
class of users ...g)


We sometimes let the users see an Autonumber, when they need to
correctly identify one record among many similar records -
transactions for example. Of course they can't change it, and we tell
them not to worry about gaps in the sequence. Once we explain that
it's just a unique "Reference Number" (we actually sometimes call it
that in the UI), they seem to do just fine with it. It's easier than
building our own sequence number.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

  #7  
Old April 29th, 2010, 01:44 AM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default What are AutoNumber Dangers

=?Utf-8?B?RnJlZA==?= wrote in
:

When it's said to never show it to the outside world, that's to
guard against anybody ever trying to change one.


Er, what? Autonumbers aren't editable, so there's no danger even
*if* somebody is tempted to change one.

The problem is that people invest MEANING in Autonumber values and
assume they are sequential and have no gaps. An Autonumber is really
just a special type of default value for a Long Integer field (you
can append any value to it you like that doesn't violate an existing
index). It can, in fact, be random (and *is* random in a replicated
Jet database).

The values of an Autonumber field have no meaning. Exposing them to
the user tempts them to invest the values with meaning, and that's
the flaw.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #8  
Old April 29th, 2010, 01:57 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default What are AutoNumber Dangers

On Wed, 28 Apr 2010 13:11:01 -0700, Barry A&P
wrote:

I recently viewed a post that said "autonumber is fine as long as you dont
display it to the user..." Everything in my database references
autonumbers!! did i make a big mistake somewhere?? i am even marking parts
with ID numbers that are generated by the autonumber field on a few of my
tables...

Should i be worried??

Do i need to seriously look into a new approach??

any comments are greatly appreciated..

Barry


Just one other caveat in addition to the good points made by the other
responders: if you ever Replicate your database (which you won't if it's in
2007 or 2010, that feature has been removed), all your autonumbers will become
random. The next three records might have autonumber values of -228412113,
1924443671 and -889225013. If that won't freak out your users nothing will!
--

John W. Vinson [MVP]
  #9  
Old April 29th, 2010, 05:09 PM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default What are AutoNumber Dangers

I am in fact using the autonumber field because i Need a Unique Identifier
for each item. I tried the Consecutive number approach but had many cases
where using DMax+1 then the last record was deleted and the number was
re-created-- I am in Access 2007 and dont know if i will ever need to
replicate (whatever that does) so for now i will cross my fingers and hope i
dont ever see -32481927 as a Unique Identifier for one of my parts.. Cross
that bridge when i get there i guess...

Thank you everybody for your input..

Barry

"Armen Stein" wrote:

On Wed, 28 Apr 2010 14:59:31 -0700, "Jeff Boyce"
wrote:

What Daniel and Fred said ...


Me too!

Access Autonumbers are designed to serve as unique row identifiers, nothing
else. As such, they are rarely suitable for human consumption.


Rarely, but not never.

If you want your users to care about some number, use Daniel's approach and
create a new field, in addition to your autonumber field. Use that new
field to hold a "sequence number". That one's OK to show the users...

(and all things in moderation... a fellow whose experience I greatly respect
quite often displays autonumbers to his user ... but he has a very high
class of users ...g)


We sometimes let the users see an Autonumber, when they need to
correctly identify one record among many similar records -
transactions for example. Of course they can't change it, and we tell
them not to worry about gaps in the sequence. Once we explain that
it's just a unique "Reference Number" (we actually sometimes call it
that in the UI), they seem to do just fine with it. It's easier than
building our own sequence number.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

.

  #10  
Old April 29th, 2010, 05:10 PM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default What are AutoNumber Dangers

Thanks Jeff

I needed to see this line

"(and all things in moderation... a fellow whose experience I greatly respect
quite often displays autonumbers to his user ... but he has a very high
class of users ...g)
"

Barry

"Jeff Boyce" wrote:

Barry

What Daniel and Fred said ...

Access Autonumbers are designed to serve as unique row identifiers, nothing
else. As such, they are rarely suitable for human consumption.

Keep using them as unique row identifiers, but don't expect your users to
understand ... OR CARE!

If you want your users to care about some number, use Daniel's approach and
create a new field, in addition to your autonumber field. Use that new
field to hold a "sequence number". That one's OK to show the users...

(and all things in moderation... a fellow whose experience I greatly respect
quite often displays autonumbers to his user ... but he has a very high
class of users ...g)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Barry A&P" wrote in message
...
I recently viewed a post that said "autonumber is fine as long as you dont
display it to the user..." Everything in my database references
autonumbers!! did i make a big mistake somewhere?? i am even marking
parts
with ID numbers that are generated by the autonumber field on a few of my
tables...

Should i be worried??

Do i need to seriously look into a new approach??

any comments are greatly appreciated..

Barry



.

 




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


All times are GMT +1. The time now is 08:22 PM.


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