Separate PK in Jxn Tbl?

[Neil]

Whenever I've created junction tables in the past, I always made the PK of
the junction table the combined pks from the two other tables. Ex: Table1
PK=A; Table2 PK=B; Junction table consists of two fields, A and B, which
together comprise the PK for the junction table.

However, I just came across some code in which the person created a junction
table with a separate PK consisting of an autonumber field, and then the two
fields.

So I was wondering how others did junction tables -- with a standalone
autonumber PK, or with a PK consisting of the PKs of the tables being
joined? And, if a standalone PK, then why?

Thanks!

Neil

 

[Phil Stanton]

I always use just the 2 primary keys but....
If I were a library lending 2 undiffentiatable copies of 1 book to the same
person, I suppose I would need an Autonumber PK plus A BookID and
BorrowerID.

Phil

 

[Roy Hann]

I always use just the 2 primary keys but....
If I were a library lending 2 undiffentiatable copies of 1 book to the
same person, I suppose I would need an Autonumber PK plus A BookID and
BorrowerID.

Hmm. Not persuaded. At best you have concealed a problem with the business
process, at worst you have allowed the very same copy to be lent multiple
times without detection.

I say this with some passion, having come home to find my cash register
receipt shows a single bottle of gin got scanned twice.

Roy

 

[David Cressey]

So I was wondering how others did junction tables -- with a standalone
autonumber PK, or with a PK consisting of the PKs of the tables being
joined? And, if a standalone PK, then why?

With a PK consisting of the FKs that reference the tables being joined.

Is that what you meant?

 

[John W. Vinson]

So I was wondering how others did junction tables -- with a standalone
autonumber PK, or with a PK consisting of the PKs of the tables being
joined? And, if a standalone PK, then why?

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).

If I do so, I will always specify a unique (non-primary) Index on the two
parent foreign key fields.

John W. Vinson [MVP]

 

[Bob Badour]

Hmm. Not persuaded. At best you have concealed a problem with the business
process, at worst you have allowed the very same copy to be lent multiple
times without detection.

I say this with some passion, having come home to find my cash register
receipt shows a single bottle of gin got scanned twice.

Roy

From that last anecdote, one can conclude one of two things: 1) gin was
only a small part of the overal liquor purchase or 2) you were too drunk
to notice.

Either way, you are a lush!

 

[Phil Stanton]

Does a bottle of Gin have a serial no? I am too poor to buy 2 at a time to
see if there is a different number Ah!

Phil

 

[Roy Hann]

From that last anecdote, one can conclude one of two things: 1) gin was
only a small part of the overal liquor purchase or 2) you were too drunk
to notice.

Actually I got the kids to buy it on the way home from school.

Roy

 

[Neil]

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).

If I do so, I will always specify a unique (non-primary) Index on the two
parent foreign key fields.

John W. Vinson [MVP]

Good to know. That makes sense, about needing the PK to refer separately to
the junction table, if that situation exists.

 

[Tony Toews [MVP]]

However, I just came across some code in which the person created a junction
table with a separate PK consisting of an autonumber field, and then the two
fields.

So I was wondering how others did junction tables -- with a standalone
autonumber PK, or with a PK consisting of the PKs of the tables being
joined? And, if a standalone PK, then why?

I always use an autonumber PK and a uniqui index set on the two FK fields.

Why? No particular good reason. One of my database rules is that all tables have
an autonumber primary key. It's also slightly easier to delete the record in code.

Now if I was to have a child table from the junction table then I would absolutely
use a autonumber primary key for ease of use when designing queries, forms and
reports.

The theorists will argue. I don't care.

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/

 

[Larry Linson]

Actually I got the kids to buy it on the way home from school.

That could have happened where I grew up -- it was a dry county, but liquor
was available from bootleggers and a few people who ran their own stills.

 

[Tony Toews [MVP]]

Would you care for a pragmatist to offer a counter argument?

You're no pragmatist.

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/

 

[David Cressey]

I always use an autonumber PK and a uniqui index set on the two FK fields.

Why? No particular good reason. One of my database rules is that all tables have
an autonumber primary key. It's also slightly easier to delete the record in code.

Now if I was to have a child table from the junction table then I would absolutely
use a autonumber primary key for ease of use when designing queries, forms and
reports.

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.

 

[Bob Badour]

tables have


record in code.


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.

 

[Brian Selzer]

I always use an autonumber PK and a uniqui index set on the two FK fields.

Why? No particular good reason. One of my database rules is that all
tables have
an autonumber primary key. It's also slightly easier to delete the
record in code.

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.

 

[lyle fairfield]

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?

 

[Larry Linson]

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

 

[Tony Toews [MVP]]

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/

 

[Tony Toews [MVP]]

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/

 

[Brian Selzer]

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!

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.