Re: Separate PK in Jxn Tbl?

Roy Hann wrote:

> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:uTvWO%23%23XIHA.1532_at_TK2MSFTNGP04.phx.gbl...
>

>>To that, I would add that the increased simplicity of using a surrogate 
>>(or artificial or autonumber) key as the primary key in place of a 
>>composite key is only half their advantage.
>>
>>The biggest problem that I have with composite keys is that they share the 
>>same fundamental problem as natural keys: using them as the primary key is 
>>allowing the fact that a primary key can change its value over time. 
>>IMHO, a primary key should never be allowed to change its value once it 
>>has been created; a assumption which will forbid the use of a composite 
>>key in many cases.  (Of course, if you don't mind to see a primary key 
>>changing its value after its creation then you are not concerned by this 
>>argument.).

That, in a nutshell, is Date's _Principle of Incoherence_.

   One specific comment I will make is that my progression has
> been the opposite of yours. I once used synthetic keys everywhere, but now
> try to limit my use of them, with wholly beneficial effects. That may be
> why I write about this with the fervor of a born-again convert.
>
> I would have a lot more sympathy for these kinds of claims if the people
> making them would give any hint that they know what the alternatives are,
> and why their solutions make sense within application development tools.
> For example, I don't think I've ever seen anyone enthusing about the liberal
> use of synthetic keys who also noted that they are aware of the possibility
> of declaring a foreign key constraint WITH ON UPDATE CASCADE. If you can
> persuade me you've looked at it and had to reject for reasons X, Y, and Z, I
> can respect that. Or tell me you know about it but your particular product
> doesn't support it and I can respect that. Or tell me that you understand
> that the DBMS handles the problem almost trivially but the application
> development tools make you write extra code and I can respect that.
>
> And what is all this tripe about composite keys making the SQL more complex?
> If I bodge up my tables with a spurious third synthetic key (skey) so that
> instead of writing:
>
> select i.description, b.cause_of_damage
> from orderitems i left join breakages b
> using (ordernr,itemnr)
>
> I can instead write:
>
> select i.description, b.cause_of_damage
> from orderitems i left join breakages b
> on i.skey = b.skey
>
> How much easier is THAT?? And at what cost?
>
> If you don't convince me that you properly understand the problems, and
> crucially, where the problems *really* lie, then you aren't going to
> convince me that your solutions are anything but cut-and-paste rote-learned
> hackery that seems elegant/sensible only to those with limited knowledge of
> very limited products. You have to establish that you are credible.
> Merely claiming years and years of experience (as others have)

I have found some people can work for 10 years and get a year's experience 10 times.

  could just
> mean they've been successfully getting away with being incompetent--and
> goodness knows, that happens, so they won't get the benefit of the doubt.
>
> Roy
Received on Sat Jan 26 2008 - 17:07:29 CET