[AccessD] Autonumber Assigned Immediately

Charlotte Foust cfoust at infostatsystems.com
Fri Sep 9 10:35:33 CDT 2005


You've got that one right, and it was a real headache.  But it is not
germane to the issues under discussion because it didn't foul up
*existing* keys.

Charlotte Foust


-----Original Message-----
From: Jim Dettman [mailto:jimdettman at earthlink.net] 
Sent: Friday, September 09, 2005 6:46 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Autonumber Assigned Immediately


John,

  Well it wasn't too long ago that JET had a bug with it's auto number
and in early versions was not reliable<g>

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John W. Colby
Sent: Friday, September 09, 2005 9:27 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Autonumber Assigned Immediately


>True, but if there is ever a bug in your storage system and your keys 
>get
scrambled, then your really up a creek.  A natural PK design would not
be as the key is always constructed from the data.

A bug where the CONTENTS of a field get scrambled is much more likely
which would affect natural keys more quickly than surrogate keys if for
no other reason than that surrogate PK keys are normally non-changeable
(read only) at the db engine level whereas natural keys by their very
definition HAVE to be read / write because the data may change.

I think arguing that the db engine MIGHT screw up is grasping at straws
whereas natural keys can and do change almost on a daily basis somewhere
in the world, an established fact, accepted by all.  I'll take the
"might" over the "does daily" every time.

John W. Colby
www.ColbyConsulting.com

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Friday, September 09, 2005 8:22 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Autonumber Assigned Immediately


John,

 <<I have used autonumbers for EVERY SINGLE PK in EVERY
SINGLE database I have ever designed (and that is a LOT of databases)
and have NEVER, even ONCE had to modify my PK, chase down FKs, add new
fields to my PK/FK etc.>>

  True, but if there is ever a bug in your storage system and your keys
get scrambled, then your really up a creek.  A natural PK design would
not be as the key is always constructed from the data.

  Granted, something that fundamental would (one hopes) get caught
pretty quick, and there are always backups.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John W. Colby
Sent: Thursday, September 08, 2005 9:54 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Autonumber Assigned Immediately


Thank you Lambert.  I have used autonumbers for EVERY SINGLE PK in EVERY
SINGLE database I have ever designed (and that is a LOT of databases)
and have NEVER, even ONCE had to modify my PK, chase down FKs, add new
fields to my PK/FK etc.  EVER, even ONCE.  How many users of natural
keys can say that?  There is no debate, just silly people who do silly
things.

John W. Colby
www.ColbyConsulting.com

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan,
Lambert
Sent: Thursday, September 08, 2005 4:53 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Autonumber Assigned Immediately


For some wild reason this issue just never goes away. I for one (in my
simple way of thinking) have never ever seen a need to use a composite
key, natural key, surrogate key, call it what you will. Dumb old
AutoNumbers fit the bill just fine for me. As you say, they ID the rows
perfectly.  To my way of thinking anyone who depends on natural keys to
maintain table relationships is asking for trouble because users have a
nasty habit of wanting to alter data.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Reuben
Cummings
Sent: Thursday, September 08, 2005 4:42 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Autonumber Assigned Immediately


If that's the argument then we could never create a perfect ID because
the data can always change regardless of how the programmer decides to
ID it. Whether I use autonumber 6 or custom ID ZX4-TD54D the data can
always chnage and both ID's can and will always get me to the proper
place.

For your scenario to be correct so that an ID identifies the data then
the programmer would need to make a routine that would take parts of
several fields and piece it together.  However, if the data changes one
of two things will happen.  One, the created ID will not match the new
data if the programming does not change it upon a data change.  Or, two,
the ID is changed and then the ID does not match an ID that may have
been written on a paper version of an application, for example, and then
there is no way to know what record relates to that paper copy.

I beleive the autonumber DOES identify the data.  The number ID's the
row. The data is in the row ID'd by the number.  Therefore the number
identifies the data.

Damn, I got drug back into this thing.

Reuben Cummings
GFC, LLC
812.523.1017


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte 
> Foust
> Sent: Thursday, September 08, 2005 3:14 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Autonumber Assigned Immediately
>
>
> You miss the point.  Autonumber 6 doesn't change but the data in the 
> row does.  My point was that Autonumber 6 doesn't identify the data in

> any way, merely the row that the data happens to be stored in.  I'm 
> not opposed to autonumbers, I use them all the time.  What I'm opposed

> to is trying to turn the autonumber into a meaningful value.
>
> Charlotte Foust
>
>
> -----Original Message-----
> From: Reuben Cummings [mailto:reuben at gfconsultants.com]
> Sent: Thursday, September 08, 2005 9:25 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Autonumber Assigned Immediately
>
>
> You're correct Charlotte, but I've also NEVER seen a case where 
> autonumber 6, for example, changed which row of data it is connected 
> to. It always ID's the same row for life.  I have never agreed to the 
> statement that the data is separate from the autonumber - they ARE in 
> the same row.
>
> IMO, the autonumber field is simply a way to create sequential 
> numbering without writing a bunch code to do so.  The fact that it can

> also be the Primary Key is just an added benefit.  Why include two 
> completely unique numbers in a row if one will do two jobs?  I'm a big

> fan of simplicity.
>
> This will lead to a ridiculous discussion - again - so I agree to 
> disagree
> ;)
>
> BTW, Charlotte, because I have never driven a nail with a screwdriver 
> that method of driving nails is, in fact, NOT a valid method.  :) I'll

> let the list know if I ever do.  However, an adjustable wrench is 
> acceptable.
>
> Reuben Cummings
> GFC, LLC
> 812.523.1017
>
>
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte 
> > Foust
> > Sent: Thursday, September 08, 2005 11:04 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Autonumber Assigned Immediately
> >
> >
> > No, it is a row identifier and therefore always points to the same 
> > row, period.  The data in that row are entirely independent from the

> > autonumber, which is what leads to all the intense discussions of 
> > "primay key" vs "unique key" vs "surrogate key", etc.  The fact that

> > you have used it this way doesn't make it any more valid a usage. 
> > After all, you can drive nails with a screwdriver too. ;->
> >
> > Charlotte Foust
> >
> >
> > -----Original Message-----
> > From: Reuben Cummings [mailto:reuben at gfconsultants.com]
> > Sent: Thursday, September 08, 2005 8:46 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Autonumber Assigned Immediately
> >
> >
> > I don't know why everyone is so adamant about no showing an 
> > autonumber. I agree technically they shouldn't be shown.
> >
> > But sometimes a simple solution is the best.  For example, we 
> > currently have some contracts to 'digitize' a state required form 
> > for several local counties.  Our solution to digitize is merely 
> > enter the data into a DB we created.  However, the paper copies have

> > to remain available. We number every form we put in so that the user

> > can then search and find a paper copy by using the number generated 
> > by the software.
> >
> > Initially we did this using the autonumber and it worked perfectly 
> > (we
>
> > had to abandon it to allow multiple entry persons for one county.
> >
> > It is a row identifier and therefore always points to the same set 
> > of data. If he wants to use it as an identifier let him.  I have.
> >
> > Reuben Cummings
> > GFC, LLC
> > 812.523.1017
> >
> >
> > > -----Original Message-----
> > > From: accessd-bounces at databaseadvisors.com
> > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of 
> > > Charlotte Foust
> > > Sent: Thursday, September 08, 2005 10:31 AM
> > > To: Access Developers discussion and problem solving
> > > Subject: Re: [AccessD] Autonumber Assigned Immediately
> > >
> > >
> > > That doesn't make sense.  If you have pull data in from other 
> > > tables
>
> > > and populated fields in a new record, the autonumber should have 
> > > been assigned, so there's something you aren't telling us.  And as

> > > someone else pointed out, the user should NEVER see the 
> > > autonumber.
>
> > > It has no
> >
> > > meaning, it merely identifies a row, not the data in the row, and 
> > > it
>
> > > should not be treated as meaningful.
> >
> >
> >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com 
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com 
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>




--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list