Re: Separate PK in Jxn Tbl?

From: Brian Selzer <brian_at_selzer-software.com>
Date: Mon, 28 Jan 2008 17:24:36 GMT
Message-ID: <odonj.910$R84.571_at_newssvr25.news.prodigy.net>


"David Cressey" <cressey73_at_verizon.net> wrote in message news:%9lnj.5893$cm6.4751_at_trndny05...
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:uzBiNoRYIHA.4172_at_TK2MSFTNGP02.phx.gbl...
>
>> This is the big problem with natural keys. When the theory was first
>> elaborated, the point was that a natural key never change its value.
>> That
>> was the point that was making the choice of a natural key on par with the
>> use of a surrogate key as the primary key of a table. However, like
> anyone
>> have discovered with experience, a natural key can change its value under
> a
>> set of various circonstances. One could argue that if a key can change
> its
>> value, than it's not a natural key but as you know, this argument bring
>> nothing in regard to help you choosing a natural key.
>>
>
> This is a specific example of a syndrome that I described more generally:
> the mismanagement of natural keys.
>
> If a key is "natural", it is managed, if at all, beyond the scope of the
> DBMS. It might be managed at the application layer, or it might be
> managed
> by people, or it might indeed be unmanaged data, like sunspots.
>
> When it is managed by other people, it is subject to mismanagement.
> Changing values that ought to be immutable is one of many ways that
> natural
> keys can be mismanaged.
>
> As I said before, I prefer to use natural keys where ever possible. If
> that's not possible due to mismanagement of the natural keys, I'll use
> synthetic keys.
>
>
>

I don't think it's productive to blame it on the users. The problem you're referring to is due to the nature of keys, and misunderstandings on the part of the database designer as to what constitutes a key. All that is required for a key to be a key is that in every possible database instance, a projection over the attributes in the key for a relation has the same cardinality as the relation. This does /NOT/ mean that a particular combination of values /always/ identifies the same individual in the Universe of Discourse, but only in the picture of the Universe that is a database instance. In other words, a particular combination of values may not /necessarily/ identify an individual, but rather may only /contingently/ identify an individual. This is the nature of keys: either the values for a key are permanent identifiers, or they're not. It has nothing to do with how well keys are managed. The values for a key may be managed perfectly, yet still not be permanent identifiers--the position of something in a list of things comes to mind. Received on Mon Jan 28 2008 - 18:24:36 CET

Original text of this message