A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Separate PK in Jxn Tbl?



 
 
Thread Tools Display Modes
  #21  
Old January 24th, 2008, 03:01 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
lyle fairfield
external usenet poster
 
Posts: 8
Default 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  
Old January 24th, 2008, 03:14 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Larry Linson
external usenet poster
 
Posts: 3,112
Default 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  
Old January 24th, 2008, 05:43 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default 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  
Old January 24th, 2008, 05:44 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default 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  
Old January 24th, 2008, 08:03 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Brian Selzer
external usenet poster
 
Posts: 32
Default 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  
Old January 24th, 2008, 11:24 AM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
JOG
external usenet poster
 
Posts: 30
Default 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  
Old January 24th, 2008, 12:09 PM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Jamie Collins
external usenet poster
 
Posts: 1,705
Default 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  
Old January 24th, 2008, 12:18 PM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Jamie Collins
external usenet poster
 
Posts: 1,705
Default 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  
Old January 24th, 2008, 12:24 PM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Jamie Collins
external usenet poster
 
Posts: 1,705
Default 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  
Old January 24th, 2008, 02:26 PM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
lyle
external usenet poster
 
Posts: 23
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:22 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.