Re: Separate PK in Jxn Tbl?
Date: Sat, 26 Jan 2008 23:35:19 -0500
Message-ID: <bSTmj.2644$so6.695_at_newssvr19.news.prodigy.net>
"JOG" <jog_at_cs.nott.ac.uk> wrote in message
news:274f1803-9fe8-4801-af35-e9a8c99cf823_at_i72g2000hsd.googlegroups.com...
> On Jan 27, 2:09 am, "James A. Fortune" <MPAPos..._at_FortuneJames.com>
> wrote:
>> Marshall wrote: >> > On Jan 26, 4:26 am, "David Cressey" <cresse..._at_verizon.net> wrote: >> >> >>When you want to delete an entry form a junction table, you almost >> >>always >> >>know the two FKs that uniquely determine the entry to be deleted. You >> >>almost never know the value of the superflous surrogate key. So it's >> >>simple >> >>to use the two FK's as the criterion for deletion than it is to look up >> >>the >> >>ID field, and then use that as the basis for deletion. >> >> > Yes, exactly. >> >> > One of the greatest benefits, and one of the fundamental >> > differences between how SQL treats data and how >> > (most) conventional programming languages treat data >> > is that in SQL we specify data by its value, instead of by >> > location. I often observe that superfluous keys in the field >> > are an attempt to make SQL data have an address, to >> > make it behave the way the programmer's mental model >> > (perhaps influenced by years of using pointers) does. >> >> > Marshall >> >> Personally, I don't take the natural keys out either, so they can still >> be used for the deletion. The thought of giving the SQL data an address >> and following a programmer's mental model did not enter into my thinking >> at all. I am not trying to give the data an order either. You've been >> listening to Celko too much. Because of his overall manner, which I >> find quite offensive, I don't even want to listen to him when he's right >> :-). Is the AutoNumber primary key a denormalization of the schema? >> Yes. Is it added for a reason? Yes again. I'm still waiting for a >> cogent reason for me to go to using natural keys. >
> * Artificial keys allow you to enter the exact same statement of fact
> twice. This would simply be nonsense.
Not exactly. Artificial key values are simply names assigned to individuals in the Universe of Discourse. I would think that it should be possible to have many different names for the same thing: considering the fact that there are a great many different languages, there must therefore be a great many words for each thing.
> * Artificial keys allows a tuple at t1 and a tuple at t2 to be
> corresponded to each other, even if they don't have a _single_
> attribute from the real world in common. This would also simply be
> nonsense.
I wouldn't call it nonsense. It is not necessary that every property that an individual exemplifies be represented in the database--only those properties that are relevant to the problem at hand need be included. In that event, if a particular individual is assigned a name at t1, and then if the values for all of the properties that are relevant to the problem at hand at t1 are compared to those from the individual with the same name at t2, it is possible for all of those properties to be different. That isn't nonsense, it just is, given the inherent incompleteness of the information in the database.
> * Referencing an artificial key in a child table can complicates
> queries - and not just with a longer restrict clause, but with a whole
> extra join that may well have been unrequired if a natural key had
> been used.
>
You left one out. In a table that has multiple natural keys, when an artificial key is added, which key values are its values surrogates for?
> So not one, but three cogent reasons of the top of my head. I wouldn't
> say there are never cases when an artificial key is useful, but they
> certainly shouldn't be hidden, and adding them blindly to every
> relation is surely just a bit silly. Regards, J.
> >> >> James A. Fortune >> MPAPos..._at_FortuneJames.com
>
Received on Sun Jan 27 2008 - 05:35:19 CET