Re: Separate PK in Jxn Tbl?

>> Your argument about the use of a DRI WITH ON UPDATE CASCADE is an
>> interesting argument and one that come back often;
>
> Really? I can put my hand on my heart and say I've never seen anyone
> suggest it when they cross-post to comp.databases.theory. I don't doubt
> it has come up, but I do doubt it is often.

Oh, the word "often" is all relative here. Of course, in a newsgroup about Access and/or SQL-Server, you won't see it coming very often because these two databases don't offer support for DRI updating on cyclic relationships. What you are more likely to see would be people complaining about the absence of such support in SQL-Server. However, I suppose that many of those peoples who are making this complaint have probably the use of natural keys behind their mind (the other strong possibility beeing the use of some form of replication); so this close the circle. I've also suppose that on other newsgroups dedicated to systems like Oracle, the discussion about this point is probably much more heated.

I should also add that if I remember correctly, this one was a feature that has been promised to be released with SQL-Server 2000 but that it didn't make the last cut and a lot of people were angry about that. Nowadays, in regard to the next release of SQL-Server 2008, peoples seem to be more interested with features such as the integration with .NET and other big stuff like that than to know if Katmai will offer support for this or not.

> First of all, updates to keys should be very rare. A fundamental property
> of a well-chosen key is that it is reasonably stable. Imposing the little
> self-discipline required to make sure you choose stable keys is
> inconsequential when compared to the overall database design effort.

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.

Now, how it's important the fact that the update to a key should be very rare? To me, there is a big difference between 0 and 1 time but there is not between 1 and 1 million times. If you have to put code in order to take into account the fact that the key can change its value, it should be the same code if the key change its value one single time for the life of the database or multiple times each day. In many cases, you are even placed in the situation where there is a strong possibility that the key will never change its value a single time for the whole lifetime of the database but that you must take into account the possibility that it might do so.

Of course, there are many possible solutions: put a lot of DRI/triggers and other pieces of code; forbid any change (and make the clients unhappy on many occasions); suggest to delete the records and recreate them from scratch or even rebuild the whole database; etc.; etc. but why bother with all these in the first place?

To me, the use of natural keys is like someone bringing me a box full of Damocles' swords: he would usually tell me to be very careful when I will put these on the ceiling. Later, when he will come back and see the empty ceiling and ask me why, I would answer him that I've put the box in the garbage bin but if he want them, he can bring the box home for free; at the condition that I'll never see them again. The fact that some theorists like these swords too is of no interest to me. If they like them, they too can bring them home for free but at the same condition, that I'll never them again.

-- 
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Roy Hann" <specially_at_processed.almost.meat> wrote in message 
news:HKqdneWeJ7ZO7QHanZ2dneKdnZydnZ2d_at_pipex.net...


> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 


> wrote in message news:%23zkseSHYIHA.4808_at_TK2MSFTNGP05.phx.gbl...

>> Your argument about the use of a DRI WITH ON UPDATE CASCADE is an 

>> interesting argument and one that come back often;

>

> Really?  I can put my hand on my heart and say I've never seen anyone 

> suggest it when they cross-post to comp.databases.theory.  I don't doubt 

> it has come up, but I do doubt it is often.

>

>> however it's not a silver buller.

>

> I implicitly allowed that it is not a silver bullet by actually suggesting 

> the kinds of reasons one might exclude it.

>

>> First of all, it's another level of complexity that you must add to the 

>> design of your database; ie, you must make sure that they are all there 

>> and no one is missing.

>

> Of course one must make sure "they are all there"; you are absolutely 

> right. But it absurd to suggest doing that is "another level of 

> complexity".  It is trivial to do it, and trivial to check that you've 

> done it by querying the DB catalogs.

>

>> Second, this DRI cannot be used with cyclic relationship with SQL-Server 

>> but with Oracle, you can.  (From your example, I believe that you are 

>> working with Oracle).

>

> I'm not.  But the fact that you are distinguishing the behaviour of 

> particular products gets close to the real problem.  The real problem is 

> that the products we use are all more or less defective, but instead of 

> clamouring to have them fixed (by establishing suitable standards and 

> following them) we promote workarounds as if they are actually desirable. 

> I have no problem at all with people describing workarounds for defects 

> but I have a major problem when it is implied that the workaround is some 

> kind of best-practice or even desirable.

>

>> On SQL-Server, you must use triggers to implement such a feature when 

>> there is a cyclic relationship. Of course, when you are dealing with tens 

>> and hundreds of relationships, this can quickly translate into a 

>> nightmare.  There is also the qestion of the diminution of performance 

>> and of general design: when you have to update multiples records on 

>> multiple tables for what should be the change of a single value in a 

>> single table make it hard to believe that this is a proper normalized 

>> database design

>

> First of all, updates to keys should be very rare.  A fundamental property 

> of a well-chosen key is that it is reasonably stable.  Imposing the little 

> self-discipline required to make sure you choose stable keys is 

> inconsequential when compared to the overall database design effort.

>

> Secondly, even having to update hundreds of tables to amend a key is only 

> about the same effort required to insert all those rows in the first 

> place. Against the background of work the system does all the time, that 

> will be inconsequential.  (Of course, if you unwisely choose a key that is 

> not stable, your argument would be more nearly correct.  But that is why 

> the long-standing advice has been to avoid keys that are not stable.)

>

>> and this situation quickly worsen if you have to take into account the 

>> correspondance with backups, reports and linked databases; all systems 

>> for which there is no automatic DRI.

>

> I don't entirely agree with all these reasons, but as I said in my earlier 

> post, there often *are* good reasons why one might not be able to use ON 

> UPDATE CASCADE in a particular product and I will take your word for it 

> that these reasons apply with the product you use.  My challenge to you 

> was to signal you know that, and you have now done so.

>

>> But why make it simpler when you can make it harder?

>

> Hm.

>

>> Finally, I don't understand your example at all.  You are introducing us 

>> to the NATURAL JOIN and USING statement that have been introduced by 

>> Oracle in its 9i version (also in MySQL and Postgres, I believe) but I 

>> fail to see what this has to do with the subject of this thread; the use 

>> of a separate PK in a junction table and its highly related topic, ie. 

>> the use of natural keys versus the use of surrogate keys.  There is no 

>> relationship at all between a NATURAL JOIN and a natural key and the 

>> Natural Join can be used as easily with a surrogate key than with a 

>> natural key.

>

> I am confused about your argument here.  I was giving counter-example to 

> disprove the claim that composite keys make the SQL code more complex, 

> which was being presented as an argument to introduce yet more, spurious, 

> synthetic/surrogate keys.  In fact if you read my example carefully, you 

> will have seen that I talked explicitly about *three* synthetic keys 

> because I aware I was already using two (order number and item number).

>

>> The only thing that is important with the Natural Join is the name of the 

>> key.  (BTW, if you were to ask me what I'm thinking about this little 

>> monstruosity, I would tell you that this is a perfect example of a 

>> Pandora box.).

>

> Well, I have to admit that I'm not over-fond of relying on names to imply 

> that two columns represent the same thing, so in fact I never use that 

> syntax.  I do prefer to assert all the conditions on all the key columns 

> explicitly and I just don't notice the few extra keystrokes when it's a 

> composite key.  But on the other hand, I find it monstrous when I see two 

> or more distinct names for colunms that do represent the same thing.

>

>> And finally, a for your request of asking me to convince you that I'm 

>> properly understand the problem here of to etablish that I'm credible: I 

>> can tell you that I have absolutely no intention of doing it

>

> And yet you have greatly increased your credibility with this post.  I 

> still disagree with what you've said, but I can see you know more about 

> what you're talking about than it seemed before.  Before, I thought you 

> were ignorant and uncurious.  Now I see you are merely wrong.  :-)

>

>> and that I have absolutely no interest at all about what you are thinking 

>> of me.

>

> That's very healthy.  You shouldn't.

>

>>The only things that are of interest to me are the arguments that I'm 

>>seeing posted here - whatever the people who might write them - but for 

>>someone who has just make a confusion between a natural key and the 

>>NATURAL JOIN, asking for such a thing make it looks very strange.

>

> I make no such confusion, and a quick glance at my earlier post will 

> confirm it.

>

> Roy

>