If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#71
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 27, 2:09 am, "James A. Fortune"
wrote: Marshall wrote: On Jan 26, 4:26 am, "David Cressey" 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. * 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. * 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. 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 |
#72
|
|||
|
|||
Separate PK in Jxn Tbl?
JOG wrote:
On Jan 27, 2:09 am, "James A. Fortune" wrote: Marshall wrote: On Jan 26, 4:26 am, "David Cressey" 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. * 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. Access programmers use forms to interact with the data. If I follow Jamie's advice and constrain the data at both the table level and in code, then your points make more sense. Right now, they're just arguments for me not to constrain the data at the table level because the reasons you gave might make natural keys preferable in that situation :-). * 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. I don't agree with that point. The child table can contain the AutoNumber primary key from the main table as a foreign key if desired. I don't see how using the natural key fields requires less joins than that. Maybe an example would help me understand what you mean. 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. Did I imply that that's what I do? James A. Fortune |
#73
|
|||
|
|||
Separate PK in Jxn Tbl?
James A. Fortune wrote:
Marshall wrote: On Jan 26, 4:26 am, "David Cressey" 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 :-). So, you object to Celko's style but consider him right?!? That's a new one. ::rolls eyes:: Is the AutoNumber primary key a denormalization of the schema? Yes. You are an ignoramus. You don't even have a clue what normalization is. The addition of an attribute to act as a simple, stable key does not affect the normal form in any way shape or manner. Is it added for a reason? Yes again. I'm still waiting for a cogent reason for me to go to using natural keys. It's the familiarity, stupid. The design criteria for keys are (and I repeat): uniqueness, irreducibility, stability, simplicity and familiarity (in no particular order.) |
#74
|
|||
|
|||
Separate PK in Jxn Tbl?
"JOG" wrote in message ... On Jan 27, 2:09 am, "James A. Fortune" wrote: Marshall wrote: On Jan 26, 4:26 am, "David Cressey" 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 |
#75
|
|||
|
|||
Separate PK in Jxn Tbl?
"James A. Fortune" wrote in message ... JOG wrote: On Jan 27, 2:09 am, "James A. Fortune" wrote: Marshall wrote: On Jan 26, 4:26 am, "David Cressey" 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. * 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. Access programmers use forms to interact with the data. If I follow Jamie's advice and constrain the data at both the table level and in code, then your points make more sense. Right now, they're just arguments for me not to constrain the data at the table level because the reasons you gave might make natural keys preferable in that situation :-). Well, that's just dumb. Checks in code can reduce database round-trips, and therefore can improve performance, but are not and cannot be a substitute for constraints on the tables. It is the constraints on the tables that keeps garbage out of 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. I don't agree with that point. The child table can contain the AutoNumber primary key from the main table as a foreign key if desired. I don't see how using the natural key fields requires less joins than that. Maybe an example would help me understand what you mean. An extra join may be needed if the natural key from the parent table is used in a restrict clause. If all you have is the artificial key from the parent table, then you have to join in order to access the natural key columns. With natural keys, the natural key values from the parent table also appear in the child table, so there isn't any need to join. Bottom line: joins of artificial keys are typically faster than joins of natural keys due to the size of the comparands, but with natural keys, fewer joins may be needed.. 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. Did I imply that that's what I do? James A. Fortune |
#76
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 27, 12:39*am, "Brian Selzer" wrote:
"James A. Fortune" wrote in messagenews:% Access programmers use forms to interact with the data. *If I follow Jamie's advice and constrain the data at both the table level and in code, then your points make more sense. *Right now, they're just arguments for me not to constrain the data at the table level because the reasons you gave might make natural keys preferable in that situation :-). Well, that's just dumb. *Checks in code can reduce database round-trips, and therefore can improve performance, but are not and cannot be a substitute for constraints on the tables. *It is the constraints on the tables that keeps garbage out of the database. If the users only access the tables through forms, conforming to best practices in Access, how are they going to get garbage into the tables? Now if you're trying to keep Jamie and his Excel SQL out of your database, that's another story :-). * 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. I don't agree with that point. *The child table can contain the AutoNumber primary key from the main table as a foreign key if desired. I don't see how using the natural key fields requires less joins than that. *Maybe an example would help me understand what you mean. An extra join may be needed if the natural key from the parent table is used in a restrict clause. *If all you have is the artificial key from the parent table, then you have to join in order to access the natural key columns. With natural keys, the natural key values from the parent table also appear in the child table, so there isn't any need to join. *Bottom line: joins of artificial keys are typically faster than joins of natural keys due to the size of the comparands, but with natural keys, fewer joins may be needed.. If you're planning on using a natural key column in the child table as part of a join then doesn't it make sense to include that field in the child table? Still waiting... James A. Fortune |
#77
|
|||
|
|||
Separate PK in Jxn Tbl?
wrote in message ... On Jan 27, 12:39 am, "Brian Selzer" wrote: "James A. Fortune" wrote in messagenews:% Access programmers use forms to interact with the data. If I follow Jamie's advice and constrain the data at both the table level and in code, then your points make more sense. Right now, they're just arguments for me not to constrain the data at the table level because the reasons you gave might make natural keys preferable in that situation :-). Well, that's just dumb. Checks in code can reduce database round-trips, and therefore can improve performance, but are not and cannot be a substitute for constraints on the tables. It is the constraints on the tables that keeps garbage out of the database. If the users only access the tables through forms, conforming to best practices in Access, how are they going to get garbage into the tables? Now if you're trying to keep Jamie and his Excel SQL out of your database, that's another story :-). There can be several forms that access the same table, so you would have to duplicate the code behind each form that accesses a table, or you can get garbage into 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. I don't agree with that point. The child table can contain the AutoNumber primary key from the main table as a foreign key if desired. I don't see how using the natural key fields requires less joins than that. Maybe an example would help me understand what you mean. An extra join may be needed if the natural key from the parent table is used in a restrict clause. If all you have is the artificial key from the parent table, then you have to join in order to access the natural key columns. With natural keys, the natural key values from the parent table also appear in the child table, so there isn't any need to join. Bottom line: joins of artificial keys are typically faster than joins of natural keys due to the size of the comparands, but with natural keys, fewer joins may be needed.. If you're planning on using a natural key column in the child table as part of a join then doesn't it make sense to include that field in the child table? Still waiting... A typical schema with artificial keys: Customer {CustomerKey, CustomerNo, ...} Key {CustomerKey}, Key {CustomerNo} Item {ItemKey, ItemNo, ...} Key {ItemKey}, Key {ItemNo} CI {CustomerItemKey, CustomerKey, ItemKey, CustomerItemNo} Key {CustomerItemKey}, Key {CustomerKey, ItemKey} CI[ItemKey] IN Item[ItemKey] CI[CustomerKey] IN Customer[CustomerKey] SOLine {SOLineKey, SOKey, SOLineNo, CustomerItemKey, Quantity, Price} Key {SOLineKey}, Key {SOKey, SOLineNo} SOLine[CustomerItemKey] IN CI[CustomerItemKey] A typical schema with natural keys Customer {CustomerNo, ...} Key {CustomerNo} Item {ItemNo, ...} Key {ItemNo} CI {CustomerNo, ItemNo, CustomerItemNo} KEY {CustomerNo, ItemNo} CI[CustomerNo] IN Customer[CustomerNo] CI[ItemNo] IN Item[ItemNo] SOLine {SO#, SOLineNo, CustomerNo, ItemNo, Quantity, Price} SOLine[CustomerNo, ItemNo] IN CI[CustomerNo, ItemNo] Now write a query that returns how many of item '12345' were sold to customer '4321' It should be obvious that with the natural keys, no joins are necessary--it's just a simple select from SOLine since all of the information is actually /in/ SOLine; whereas, with the artifical keys, several joins are required because in order to query by item number and customer number, SOLine must be joined to CI which must then be joined to Customer and Item. |
#78
|
|||
|
|||
Separate PK in Jxn Tbl?
"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please)
wrote in message ... 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 |
#79
|
|||
|
|||
Separate PK in Jxn Tbl?
Roy Hann wrote:
"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please) wrote in message ... [snip] 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 disagree. You give him too much credit. 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. :-) I suspect that is wishful thinking on your part. |
#80
|
|||
|
|||
Separate PK in Jxn Tbl?
"Marshall" wrote in message ... an attempt to make SQL data have an address Exactly. Literally hundreds of attempts to sell snake oil in c.d.t. can be reduced to precisely this statement. We've all said variations of the above, but I've never seen it put so succintly. As far as the MS Access newsgroups that this discussion is posted to, I can't speak to how well your summary extends to their mental model. But many of them seem to write as if contents as determined by address were the fundamental paradigm of data. |
Thread Tools | |
Display Modes | |
|
|