Jim Dettman
jimdettman at earthlink.net
Fri Sep 9 07:11:36 CDT 2005
Lambert, I can see the old OK debate coming back<g>. Actually, you argued against yourself in your last post where you said someone might want to prefix an Alpha that represents a branch code; you've now crossed the line from meaningless to meaning full. You now have the branch code stored in two places (once in the key and once in the table), so it's possible to get a mis-match. You've also now forced the key to change if the branch code changes. If you say, "well I wouldn't put the branch code in the table a second time", then you've broken the first rule of normalization, which is that all values should be atomic. The key in this case is a composite value. Surrogate keys only exist and make sense because of real world limitations in current computer systems. But in terms of relational theory, their fallacy is that they do not have any meaning in relation to the data. Jim. -----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