Jim Dettman
jimdettman at verizon.net
Tue Mar 1 12:37:23 CST 2011
<<The only reliable solution was to move all the tables to auto PKs but it cost the client a fair chunk of change.>> That's really not true. A proper design, natural key or not would have solved the issue. <<For that reason I have never inflicted natural keys, on a client, no matter how strong the temptation.>> Neither do I, but that's simply for performance reasons. However it does mean that I need to maintain one additional index in a lot of cases. JimD. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence Sent: Tuesday, March 01, 2011 12:17 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access and SQL Server Many years ago I was taking over an Access project as the clients were having problems with their invoices. After about two days I discovered the problem with the invoice. It appears that the subform was connected to the main form by grouping together 3 fields, creating natural foreign key between the two tables. By some odd set of bad luck certain combinations of this key hash matched another unrelated key value and the sub form data was pulling from multiple invoice details. The only reliable solution was to move all the tables to auto PKs but it cost the client a fair chunk of change. For that reason I have never inflicted natural keys, on a client, no matter how strong the temptation. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan Sent: Monday, February 28, 2011 3:00 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access and SQL Server I see a lot of sense in it having a separate Autonumber PK. This is a classic case of why you should not use a natural key as your PK. What happens when the Description changes and the existing Code is no longer an accurate short representation of Description? Do you change it throughout all the tables which store it or do you leave your customer with strange Codes which don't match the description (And please don't tell me that you use Relationships with "Cascade Update" turned on.) -- Stuart On 28 Feb 2011 at 17:36, Jim Dettman wrote: > Stuart, > > <<Do you ever use anything other than longs for PK? If so, why?>> > > Occasionally on a lookup table if a client insists on having a short > code > along with a description. Then I do this: > > LookupCode - Text - PK > Description - Text > > as I don't see any sense in doing this: > > LookupID - Autonumber - PK > Code - Text - CK > Description - Text > -- 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