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