[AccessD] Access and SQL Server

Debbie Elam delam at zyterra.com
Tue Mar 1 20:17:54 CST 2011


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.)
>>>
>>>



More information about the AccessD mailing list