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 |
#21
|
|||
|
|||
Separate PK in Jxn Tbl?
"Brian Selzer" wrote in
: Only an idiot would have a rule for no particularly good reason. Only an imbecile would follow such a rule. A strong argument can be made for using autonumber primary keys--especially if the target DBMS doesn't support FOR EACH ROW triggers--but to just blythely add them for no particularly good reason is a recipe for disaster. A clear understanding of how and when they can be used and why is critical or you run the risk of a corrupt database. We often follow certain practices which we may describe as rules. These customs may simplify our work, or contribute to its success. Tony is a very experienced Access developer; readers of Comp Databases Ms- Access have benefited from his sharing that experience, sometimes in encapsulated form, as when he describes a usual, customary, or generalized course of action or behaviour as a rule. I follow the same rule. Having an auto-number primary key in each table simplifies the establishment of relationships among tables. In Access, the primary key auto-number ensures that forms bound to the table will be editable, although any unique non-null index will serve that purpose. In scripts using ADO, the primary key auto-number provides an identifier for update and delete actions. Could you cite instances of databases typically used with Access that have been corrupted by auto-number primary keys? |
#22
|
|||
|
|||
Separate PK in Jxn Tbl?
"David Cressey" wrote
Sometimes, theory IS practical. (Some would say always). A pragmatic person would at least listen to the arguments of theorists before dismissing them. In this case, of course, it is immaterial, because Tony is correct that Jamie is not a pragmatist, at least not demonstrably so in his posts that I have observed. Is there a category of "argumentist"? Larry |
#23
|
|||
|
|||
Separate PK in Jxn Tbl?
"Brian Selzer" wrote:
Only an idiot would have a rule for no particularly good reason. Only an imbecile would follow such a rule. A strong argument can be made for using autonumber primary keys--especially if the target DBMS doesn't support FOR EACH ROW triggers--but to just blythely add them for no particularly good reason is a recipe for disaster. My reasons are, in my opinion, good reasons. Not great but good. You don't like them? Tough. A clear understanding of how and when they can be used and why is critical or you run the risk of a corrupt database. Umm, not that you care I'm sure but my web pages on Microsoft Access corruptions http://www.granite.ab.ca/access/corruptmdbs.htm are the definitive resource on the web. And there have never been any Access corruptions during to autonumber primary keys that I can recall. And I've likely read just about every posting on that topic in the last eight or ten years in the comp.databases.ms-access and the microsoft.public.access.* newsgroups. However my knowledge is practical not theoretical. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#24
|
|||
|
|||
Separate PK in Jxn Tbl?
Bob Badour wrote:
The theorists will argue. I don't care. Sometimes, theory IS practical. (Some would say always). A pragmatic person would at least listen to the arguments of theorists before dismissing them. The theorists won't argue. The theorists will simply point out the inherent stupidity of abdicating thought for simplistic recipes. The invincibly ignorant won't care. They never do. Hey, I thought you had plonked me. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#25
|
|||
|
|||
Separate PK in Jxn Tbl?
"Tony Toews [MVP]" wrote in message ... "Brian Selzer" wrote: Only an idiot would have a rule for no particularly good reason. Only an imbecile would follow such a rule. A strong argument can be made for using autonumber primary keys--especially if the target DBMS doesn't support FOR EACH ROW triggers--but to just blythely add them for no particularly good reason is a recipe for disaster. My reasons are, in my opinion, good reasons. Not great but good. You don't like them? Tough. So now they're good reasons? In your earlier post, you said they weren't good reasons. Can't you make up your mind? You also haven't stated your reasons. How can I like them or not like them? I don't know them! A clear understanding of how and when they can be used and why is critical or you run the risk of a corrupt database. Umm, not that you care I'm sure but my web pages on Microsoft Access corruptions http://www.granite.ab.ca/access/corruptmdbs.htm are the definitive resource on the web. And there have never been any Access corruptions during to autonumber primary keys that I can recall. And I've likely read just about every posting on that topic in the last eight or ten years in the comp.databases.ms-access and the microsoft.public.access.* newsgroups. I was not speaking of corruption due to disk failures; I was instead referring to permitting garbage into the database due to the misuse of auto-number primary keys. However my knowledge is practical not theoretical. I gained most of my knowledge the hard way as well, but that doesn't mean that one shouldn't seek to understand and apply the theory. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#26
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 24, 7:03*am, "Brian Selzer" wrote:
"Tony Toews [MVP]" wrote in messagenews:6l5gp3hle4cn2lin154h4ip0288b0pgl0v@4ax .com... "Brian Selzer" wrote: Only an idiot would have a rule for no particularly good reason. *Only an imbecile would follow such a rule. *A strong argument can be made for using autonumber primary keys--especially if the target DBMS doesn't support FOR EACH ROW triggers--but to just blythely add them for no particularly good reason is a recipe for disaster. My reasons are, in my opinion, good reasons. *Not great but good. *You don't like them? *Tough. So now they're good reasons? *In your earlier post, you said they weren't good reasons. *Can't you make up your mind? *You also haven't stated your reasons. *How can I like them or not like them? *I don't know them! No, it looks like Tony's reasons are secret, and may only be gleaned from a romantic evening of fine wine and barry white. A clear understanding of how and when they can be used and why is critical or you run the risk of a corrupt database. Umm, not that you care I'm sure but my web pages on Microsoft Access corruptions http://www.granite.ab.ca/access/corruptmdbs.htmare the definitive resource on the web. * And there have never been any Access corruptions during to autonumber primary keys that I can recall. *And I've likely read just about every posting on that topic in the last eight or ten years in the comp.databases.ms-access and the microsoft.public.access.* newsgroups. I was not speaking of corruption due to disk failures; I was instead referring to permitting garbage into the database due to the misuse of auto-number primary keys. An actual example I experience springs to mind - I have witnessed a database where student projects were recorded via a schema of Project Partners:{id:autonumber, RoleAerson, RoleBerson}, with PK(id). None of the partnerships were aware of any "id" in the real world, and simply submitted their partnership choices on paper to admin. A clerical error resulted in 2/3 of the data being entered twice, which left a lot of people flapping about the number of markers required until the error was found. If the schema had used the natural {RoleA, RoleB} key there would have been no issue. But then for all I know, MS Access might allow duplicates anyhow.... However my knowledge is practical not theoretical. I gained most of my knowledge the hard way as well, but that doesn't mean that one shouldn't seek to understand and apply the theory. Tony -- Tony Toews, Microsoft Access MVP * Please respond only in the newsgroups so that others can read the entire thread of messages. * Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm * Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/- Hide quoted text - - Show quoted text - |
#27
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 24, 10:24 am, JOG wrote:
An actual example I experience springs to mind - I have witnessed a database where student projects were recorded via a schema of Project Partners:{id:autonumber, RoleAerson, RoleBerson}, with PK(id). None of the partnerships were aware of any "id" in the real world, and simply submitted their partnership choices on paper to admin. A clerical error resulted in 2/3 of the data being entered twice, which left a lot of people flapping about the number of markers required until the error was found. If the schema had used the natural {RoleA, RoleB} key there would have been no issue. My experience, also. That's not to say that everyone who uses autonumber PKs neglects to put constraints on alternate/candidate keys but I do encounter autonumber problems frequently i.e. tables where the only unique constraint/index is a PK on the autonumber column, which actually *facilitates* the inclusion of duplicates by giving the developer a false sense of security: "I've added a PK, I've done my bit." Being able to uniquely identify duplicates is of no comfort to me. Who do we have to blame for this state of affairs? The Access interface, IMO. If you omit to define the PK when creating a table in Design View, upon saving you get prompted to add a primary key (good) but instead of providing a way to choose existing columns, the offer is to add an autonumber. If you haven't got a key to begin with, autonumber cannot provide you with one unless you expose it to end users which everyone says you shouldn't do (everyone except Tony Toews, naturally g). The message even asserts that you need a PK to be able to create Relationships that reference the table which isn't true of Access Relationships (which are so vague they can even be based on columns of different respective data types) nor Jet foreign keys (which can be based on a unique constraint/index). BTW in Access 2007 you now just get given an autonumber PK by default, which you would then have to go to the trouble to remove. it looks like Tony's reasons are secret, and may only be gleaned from a romantic evening of fine wine and barry white. Hmm, I once tried this with Tony [TIC]: I spent hours cooking him his favourite dish, getting the seasoning just right. And what does he do when we've sat down? He picks up the salt and, just as he was about to apply it to the food I'd lovingly prepared, I cried out, "Tony, aren't you going to test it for seasoning first? I think you'll find it is just fine." He replied, "I always add salt to my food. Why? No particular good reason. One of my rules is that all food needs extra salt." I was about to protest when he added, "You don't like my rules? Tough. I don't care." Well, you can imagine how hurt I was, reader. Not as much as Tony after I'd thrown him off the balcony, though. Jamie. -- |
#28
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 24, 2:14 am, "Larry Linson" wrote:
A pragmatic person would at least listen to the arguments of theorists before dismissing them. In this case, of course, it is immaterial, because Tony is correct that Jamie is not a pragmatist, at least not demonstrably so in his posts that I have observed. Care to back up your claim with some evidence? No, you are mistaken. I *frequently* (to the point of boring people, I'm sure) back up my assertions with Jet SQL code, VBA demos, links to Microsoft articles and the Access Help, and stories from the field. This thread is a good example of that. Is there a category of "argumentist"? I think the word you are looking for is "critic". Jamie. -- |
#29
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 24, 11:09 am, Jamie Collins wrote:
Tony's reasons are secret, and may only be gleaned from a romantic evening of fine wine and barry white. Hmm, I once tried this with Tony [TIC] I also tried it with Bob Badour [TIC] but he merely dismissed my simplistic recipes and wanted more plonk. Jamie. -- |
#30
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 24, 6:09 am, Jamie Collins wrote:
He picks up the salt and, just as he was about to apply it to the food I'd lovingly prepared, I cried out, "Tony, aren't you going to test it for seasoning first? I think you'll find it is just fine." He replied, "I always add salt to my food. Why? No particular good reason. One of my rules is that all food needs extra salt." Thank you. I've learned that Tony and I share two rules. |
Thread Tools | |
Display Modes | |
|
|