Re: Design approaches about primary key

Comment in-line.

Van Messner wrote:

> Hi Georg:
>
> As you can see from these responses the answer is "it all depends". A
> couple of points about meaningful primary keys.
>
> 1. Can you guarantee the meaning will not change over time? Take a
> company with fifty offices and an offices table. If office_id is the
> primary key and you assign STL as the value for the StLouis office, what
> happens when that office is moved to Milwaukee? The "meaningful" key now is
> misleading at best.

This is a poor example. If the entire office moves intact the key stays the same. If the business closes one office but opens a different one and wishes to retain the original records intact and separate from the new ones they just create a new PK and reassign the personnel and assets.

The issue is not the PK value ... the issue is how management wishes to have the operational change reported in the future. It isn't a technology decision ... it is a management decision.

> 2. Sometimes you just can't find a meaningful key. Consider a table of
> persons. What fields, known to you, could you possibly pick to uniquely
> identify a person? Not surname, not surname + firstname + middle initial.
> Not social security number or DNA since those are usually unavailable to
> you.

I'd disagree on SSN but it is not guaranteed to be unique and of zero value to an international organization. In the case you describe the only valid solution is a surrogate "PERSON_ID" value.

> 3. Sometimes you have a problem whether you choose meaningful or
> meaningless keys. If you had a table of countries a few years back, no
> matter whether the primary key was a sequence or the United Nations country
> code, when the Soviet Union split into multiple countries you had a problem.

Not at all. It was easily handled. Records related to the USSR stopped having inserts and updates and new records were created for the new entities.

This is no different from a wholesale-distribution environment where some customers go out of business and new customers are developed. There is no technological or business reason to treat them differently.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)