Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Design approaches about primary key

Re: Design approaches about primary key

From: Van Messner <vmessner_at_bestweb.net>
Date: Fri, 28 Nov 2003 16:22:29 -0500
Message-ID: <vsffaogt91d47@corp.supernews.com>

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1070043650.390694_at_yasure...
> 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.

A matter of design and method I suppose. You don't like public synonyms, I don't like to change primary keys.

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

That's exactly the point. As I said "sometimes you just can't find a meaningful key" .

>
> > 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)
>
Received on Fri Nov 28 2003 - 15:22:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US