[AccessD] Access and SQL Server

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




More information about the AccessD mailing list