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 |
#61
|
|||
|
|||
Separate PK in Jxn Tbl?
Bob,
Here's a programming tip for you: prozac. "Bob Badour" wrote in message ... Larry Daugherty wrote: Every dog gets one bite. Then they go into the bs/Kill file. Did you note the net contribution to the subject at hand? Their commentary serves only to identify and characterize *themselves* My hope is to make meaningful contributions to those who are actually developing applications or learning to do so. Your hope is futile in the face of your ignorance and laziness. Keep up the good work! I'll try. "Tony Toews [MVP]" wrote in message ... "Larry Daugherty" wrote: This boil up is a variation of the "Autonumber vs. Natural Key" religious wars that sweep the Access groups on even numbered(?) years. In the meantime OP is probably trying to hide the matches with which he started the fires.... chuckle Yup, it's amazing how this all happens. To me the particularly amusing part is that we're generally quite civil in our discussions that stay in the Access groups. Ignorance is bliss. I'm saddened, although not surprised, at the comments from a few regulars from the c.d.theory newsgroup using words such as idiot, imbecile and invincibly ignorant. Precious few pleasant ways exist to express unpleasant truths. However, intellectual honesty requires the expression of truths regardless of appeal to oneself or to others. |
#62
|
|||
|
|||
Separate PK in Jxn Tbl?
"Jamie Collins" wrote in message ... On Jan 23, 12:45 am, "Neil" wrote: I've done both; the separate primary key is (strictly speaking) never necessary, but it can be handy if the junction table is itself related one-to-many to an additional table or tables. Access doesn't make multifield foreign keys all that easy to use (e.g. you can't use them in a combobox without some messy code). Good to know. That makes sense, about needing the PK to refer separately to the junction table, if that situation exists. Why do you say "needing the PK"? JohnV no doubt makes a good case when considering bound controls in Access but I would point out that he also said the PK is "never necessary, but it can be handy". OK, I'll rephrase: "That makes sense, about the PK coming in handy to refer spearately to the junction table, if that situation exists." Better? :-) |
#63
|
|||
|
|||
Separate PK in Jxn Tbl?
"Larry Daugherty" wrote in message ... In the meantime OP is probably trying to hide the matches with which he started the fires.... Indeed. :-| |
#64
|
|||
|
|||
Separate PK in Jxn Tbl?
wrote in message ... On Jan 25, 9:12 am, Jamie Collins wrote: (quote) What part of simpler don't you understand :-). Only one expression in the ON is simpler. Needing less indexes is simpler. Not having to look for your multi-key fields is easier, although your point that Relationships can handle that is valid. If the AutoNumber key has a one-to-one relationship with the multi-key fields then it's fine to use it. There's no down side that I can see. I also like to rely on coding to detect inconsistent data rather than on error trapping, so I have to check the multi-key values anyway before adding a new record. I think that your idea about enforcing constraints at both the table level and in code is an excellent idea. The OP wanted to know what people did and why. I still don't see any reason put forward for me to change to a multi-field key. Are totals queries easier when multi- field keys are used? BTW, "reduced the amount of denormalization" works just as well. Real databases experience denormalizing influences. (end quote) Simplicity is in the eye of the beholder. I think it's simpler to rely on constraints enforced by the DBMS to prevent duplicate entries than it is to write code to accomplish the same thing. 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. The above comment depends entirely on how you go about organizing you code. I like to keep my code simple. At least "simple" in my own eyes. The use of multi-key fields in star schemas doesn't make individual queries any simpler. But it expands by orders of magnitude the number of different combinations that can be used as selection criteria when computing totals or other aggregates. This makes the entire system simpler, on a lerger scale. I realize that star schema discussions may be out of place in MS access newsgroups. The same is true in c.d.t. There is almost nothing of theoretical interest in star schemas. |
#65
|
|||
|
|||
Separate PK in Jxn Tbl?
Roy Hann wrote:
"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please) wrote in message ... 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.). I have decided not to respond to this post in detail because there isn't a single point it makes that I agree with (as stated). I really don't know where to start. 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 |
#66
|
|||
|
|||
Separate PK in Jxn Tbl?
Hi Sylvain,
First, let me thank you for being so kind as to volunteer the information that you are a Most Vociferous Person (MVP). It does a fair service to the world when the self-aggrandizing ignorants self-declare that information. Sylvain Lafontaine wrote: 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. At this point, a prudent man would Plonk! you while mentally citing Date's _Principle of Incoherence_. Never the prudent man, instead, I observe the absurdity of your suggestion that adding features, structures or attributes increases simplicity. What nonsense! 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. I find your absolutism foolish suggesting ignorance and/or stupidity. The design criteria for keys a uniqueness, irreducibility, simplicity, stability and familiarity (in no particular order). If any criterion is absolute, it is uniqueness not stability. (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.). This is not only a theoritical argument as many interfaces - like Access - won't like to see a primary key that could change it value. It is not a theoretical argument at all. You simply regurgitate ignorance and stupidity. [remaining nonsense snipped] Plonk! |
#67
|
|||
|
|||
Separate PK in Jxn Tbl?
David Cressey wrote:
wrote in message ... On Jan 25, 9:12 am, Jamie Collins wrote: (quote) What part of simpler don't you understand :-). Only one expression in the ON is simpler. Needing less indexes is simpler. Not having to look for your multi-key fields is easier, although your point that Relationships can handle that is valid. If the AutoNumber key has a one-to-one relationship with the multi-key fields then it's fine to use it. There's no down side that I can see. I also like to rely on coding to detect inconsistent data rather than on error trapping, so I have to check the multi-key values anyway before adding a new record. I think that your idea about enforcing constraints at both the table level and in code is an excellent idea. The OP wanted to know what people did and why. I still don't see any reason put forward for me to change to a multi-field key. Are totals queries easier when multi- field keys are used? BTW, "reduced the amount of denormalization" works just as well. Real databases experience denormalizing influences. (end quote) Simplicity is in the eye of the beholder. I tend to disagree. I suspect one can quantify simplicity and complexity. I think it's simpler to rely on constraints enforced by the DBMS to prevent duplicate entries than it is to write code to accomplish the same thing. Using the dbms uses fewer tools, fewer concepts, fewer computational models, fewer structures, fewer machines. I suggest the observed simplicity is more than a matter of perspective or opinion. [further demonstrations of simplicity snipped] |
#68
|
|||
|
|||
Separate PK in Jxn Tbl?
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 |
#69
|
|||
|
|||
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; however it's not a silver buller. 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. 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). 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 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. But why make it simpler when you can make it harder? 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. 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.). 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 that I have absolutely no interest at all about what you are thinking of me. 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. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) "Roy Hann" wrote in message ... "Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please) wrote in message ... 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.). I have decided not to respond to this post in detail because there isn't a single point it makes that I agree with (as stated). I really don't know where to start. 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) 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 |
#70
|
|||
|
|||
Separate PK in Jxn Tbl?
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. James A. Fortune |
Thread Tools | |
Display Modes | |
|
|