Debbie
delam at zyterra.com
Wed Mar 2 08:33:22 CST 2011
Additional indexes are another matter entirely. They are appropriate and needed. Relationships with other tables should be immutable and not subject to data entry. Don't confuse the two. Debbie Sent from my iPhone On Mar 2, 2011, at 7:04 AM, "Jim Dettman" <jimdettman at verizon.net> wrote: > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >