Jim Dettman
jimdettman at verizon.net
Wed Mar 2 07:04:14 CST 2011
Debbie, I bet you use a natural key in your app without even thinking about it as such. My question would be, how in your app do you prevent a patent from being entered more then once? Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Debbie Elam Sent: Tuesday, March 01, 2011 09:18 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access and SQL Server I do as well. I have run into problems every time I have used (developed by others) databases with natural keys. I will NEVER use them for the following reasons: 1. Real data can ALWAYS change. I do not care how immutable it is supposed to be, data changes. Just ran into a problem in reports out of a CRM database. One magazine has changed names 3 times in 8 years. They still want info tracked together, but the natural key of a short code based on the name has changed (sigh). 2. Real Data is subject to typos. Even the best typist can realize a problem happened after data has been entered. Fix it and the relationship is crap without cascade updates. 3. Real data is never as unique as you may think. This is why natural keys usually evolve into compound keys. Had a patent database that used docket numbers as a natural key. As they supported additional countries, they added country. As addendum were added to the patent, refines were added. Now this 3 field compound key was a nightmare to work with. To top it off, you guessed it, problem 1 reared it's head too. Rare occurrence, but in a database of almost 100,000 patents, it probably occurred a few times a month. Headache every time it happened. Debbie On 3/1/2011 1:12 PM, jwcolby wrote: > In fact I do that. It establishes the PKID and it is there when I > need to use that as a FK in another table, which is more often than > one would think. > > If I need a table, I need a autoincrement PKID. > > John W. Colby > www.ColbyConsulting.com > > On 3/1/2011 1:40 PM, Jim Dettman wrote: >> >> So on a many to many linking table you would do this: >> >> tblBooksAndAuthors >> LinkID - Autonumber - PK >> AuthorID - Long - FK to tblAuthors - CK-A >> BookID - Long - FK to tblBooks - CK-B >> >> And not simply: >> >> tblBooksAndAuthors >> AuthorID - Long - FK to tblAuthors - PK-A >> BookID - Long - FK to tblBooks - PK-B >> >> and eliminate an index? If so, why not? >> >> Jim. >> >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby >> Sent: Tuesday, March 01, 2011 12:47 PM >> To: Access Developers discussion and problem solving >> Subject: Re: [AccessD] Access and SQL Server >> >> When I create a table in any datastore, the first thing I do is >> create an >> autoincrement PK. I no >> longer even think about it - if need a table, I need an autonumber pk! >> >> I then proceed to create the fields. >> >> John W. Colby >> www.ColbyConsulting.com >> >> On 3/1/2011 12:17 PM, Jim Lawrence wrote: >>> 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.) >>> >>> -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com