Re: Separate PK in Jxn Tbl?

"Roy Hann" <specially_at_processed.almost.meat> wrote in message news:Mt2dnd8_2Y5tewDanZ2dneKdnZydnZ2d_at_pipex.net...
> "David Cressey" <cressey73_at_verizon.net> wrote in message
> news:%9lnj.5893$cm6.4751_at_trndny05...
> >
> > 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 think you may be asking too much of a natural key. I always took
"stable"
> to mean *almost* never changes ratherer than meaning absolutely immutable
> (which I take to mean "intrinsically incapable of changing"). But
whatever
> one thinks it should mean, one always has to accommodate the possibility
> that a key value will be mis-typed during manual entry by a user, and
> therefore that it will have to be corrected. Mismanagement is, as you
> rightly say, a fact of life.
>

You are right. Whether or not mismanagement is a sufficient reason for distrust of a natural key depends on the degree of mismanagement. I should have made that explicit in my earlier post.

> But that's not usually a sufficiently good reason to introduce a synthetic
> key. I have found it possible to tolerate quite a lot of updates to
> supposedly stable keys using ON UPDATE CASCADE, with imperceptible
> response-time costs. (One can of course measure the costs, but that's not
> what counts.) A key would have to be really unstable to justify using a
> synthetic key IMO.
>

One problem with cascading updates occurs when you want to relate data still in the database to data that was extracted from the database at an earlier point in time. This includes, but is not limited to, data in the log file. Received on Mon Jan 28 2008 - 17:09:24 CET