Re: Natural keys vs Aritficial Keys

"Walter Mitty" <wamitty_at_verizon.net> wrote:

>When I learned databases, about a quarter of a century ago, the preferred
>practice was to use natural keys to refer to individual table rows, unless
>there's some real good reason to go with an artificial key. I've run into a
>few cases where I chose to use an artificial key, but most of the time I've
>used a natural key. (I should clarify: if a university's registration
>office assigns each student a StudentID, before entering that student into
>the database, I'm treating that key as "natural" in the context of the
>database, even though one might argue that it's "artificial" in some other
>context.)

That's a decent natural key. But life is seldom so clear.

Consider names. My brother has the same birth date, first, middle and last name as a career criminal in Canada. And we have a somewhat rare last name. It's a pain for him to cross the border into the US. He allows himself an extra hour. Although they must now have photo's in the US Customs computers as lately the delay as been quite a bit shorter.

Consider business name. Duplicate business names can exist in adjacent provinces, states or countries.

Now what happens when you get to the child tables? For example student/course table. What do you use as a primary key? Student ID, Course Number? What if a student takes the course a second time? Now the primary key needs three fields and some logic to ensure the third field is unique. What about child tables of that child table? Now it's a table with four fields, or more in the primary key.

>Every time the issue of natural keys comes up in Stackoverflow.com, the
>prevailing view seems to be that the best primary keys are artificial and
>opaque. And responses that take this view get the votes. They emphasize
>efficiency (mainly efficiency in the index that you usually get
>automatically when you declare a PRIMARY KEY). But data integrity seems to
>be forgotten, here.

How does this affect data integrity?

When there is a possibility of duplicate data we should be presenting the users with the current data that is a close match so the user can decide if the person/company/whatever is already in the system or not. For example the first two letters of the first and last name give surprisingly few matches in a database I have of 10,000 names. Even Jo Sm for Joan Smythe.

>The same is not generally tru when the conversation
>turns to referential integrity. Slowly but surely the programming community
>seems to heve been dragged, kicking and screaming, into turning over
>referential integrity enforcement to the DBMS.

I don't quite understand that the problem is with turning referential integrity over to the DBMS.

I'm quite happy with an artificial primary key (autonumber in Access) and unique indexes in data fields as appropriate.

That said Access forms and reports don't handle multifield artificial keys very well. The wizards, for example, don't handle them at all. So you're first to labouriously enter the field names by hande in the various properties such as a subforms Link Child Fields and Link Master Fields properties.

I should point out that
1) I build simple and moderately complex applications in MS Access so that clearly colours my viewpoints. A product many readers of my postings in this newsgroup hold in derision.
2) I look at this from both the DBAs viewpoint and the developers viewpoint although again many folks in this newsgroup would not consider me a DBA of any sort. 3) I've managed to irritate a number of the folks in this newsgroup with my postings in the past. As a result I've been plonked by a few. Oh well. 3a) As a result if one or two of those read replies to me they will be complaining that they were forced to read my opinion. <shrug>

Tony

-- 
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/