[AccessD] Autonumber Assigned Immediately

John W. Colby jwcolby at colbyconsulting.com
Fri Sep 9 10:27:26 CDT 2005


Yes, it absolutely is, and equally possible (and likely) to scramble the
data in the field that you are using for part (or all) of a natural key.

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 11:20 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Autonumber Assigned Immediately


Lambert,

<<magically leave the data alone so you can still construct your natural
keys?>>

 The bug was in the generation of the key value, not messing up the field
itself.  My point was, since it's the DBS system that controls the field, it
would be possible for a software bug to scramble an autonumber field and
leave everything else untouched.

Jim.

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


If this mysterious data storage bug trashes autonumbers, how does it
magically leave the data alone so you can still construct your natural keys?

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