Re: PRIMARY KEYS [somewhat OT]

On 5/20/03 2:50 PM, "Mark Wilson" <mwilson13(at)cox(dot)net> wrote:

>
> On Tuesday, May 20, 2003, at 08:41 AM, Karsten Hilbert wrote:
>
>> And - if you agree with database theory - a bad one at that.
>> Supposedly primary keys should be void of any meaning bar
>> their primary key-ness. [snip]
>
> I think a database "theory" that says primary keys should be void of
> any business meaning is a bad theory, and is certainly not required by
> the relational model.

True, the relational model doesn't require this. But it remains a very, very
good idea.
>
> As one example, a primary key with business meaning assures that one
> does not have duplicate records (if the data model is otherwise
> correct).

The problem is simply this. A good primary key (good from the brute vantage
point of the database, which is what we're designing) should not be subject
either to change or to loss of uniqueness. If a PK does change,it's true
that you can handle this by some programmatic means such as adding
cascading update rules to any foreign key constraints. So change is
irritating but not necessarily catastrophic. But loss of uniqueness is
catastrophic.

So the data modeler's job is to make keys that are least likely to change or
lose uniqueness. The best way (IMHO) to guarantee this is to make sure they
have no existence in the problem domain (the "real world") AT ALL. I'm
afraid it hasn't been my experience that "a primary key with business
meaning assures that one does not have duplicate records", because business
rules and business reasons are subject to change that the data modeler can't
control. And they do change. Or, in the example another poster offered, the
Social Security Number, the supposedly unique key is not, because someone
messed up when generating it.

So my own personal rule is never to subject the necessary characteristics of
keys to any of the ill winds that blow outside the database. These keys
should be owned, generated and maintained by the application. If the outside
world then wants to use them, well, by all means! But, from the vantage of
the data modeler, these keys serve one and only one function, which is to
uniquely identify rows in certain tables in a certain database
implementation.

So my own rule, anyway, is that primary keys should be like water --
elemental, nigh-invisible, colorless, flavorless, and completely under the
control of the application designer.

Just my two bits' worth.

-- sgl

=======================================================
Steve Lane

Vice President
The Moyer Group
14 North Peoria St Suite 2H
Chicago, IL 60607

Voice: (312) 433-2421 Email: slane(at)moyergroup(dot)com
Fax: (312) 850-3930 Web: http://www.moyergroup.com
=======================================================