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

 

[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!

 

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

 

[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/

 

[David Cressey]

(quote)
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....
(/quote)

I have begun to mess around with MS Access, as a retirement hobby, after
working professionally with some SQL DBMS products.

AFAIK, MS Access enforces the no duplicates rule, and the no missing data
rule, for every PK that's declared. There are other ways to express these
constraints, but the easiest way to get them is to declare a PK.

For that reason, I prefer not to create a new ID with an autonumber for any
junction table. Tony have have his reasons for going the other way. Until
I know what they are, I remain unpersuaded.

The fact tables in a star schema have the same property as a junction table,
except that the number of FKs that are components of the PK may be larger
than 2, and generally is larger.


I've just started messing with star schemas in MS Access. It's too soon for
me to offer even a guess as to whether this is a smart idea or a stupid
idea. All I know is that it will provide some cheap amusement for my
retirement.

 

[Tony Toews [MVP]]

I just think you're all damn brave for using Access in the first
place.

Why? It works and works well. If you have too many users or remote users bolt on a
SQL Server backend. Now you can have thousands of users.

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/

 

[James A. Fortune]

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

Whenever I have multiple key fields, natural or not, I create an
AutoNumber PK for pragmatic reasons. The main reason is that it makes
it easier to create the joins. The theorists are champions at joining
tables and don't have to be concerned with the complexity of the SQL
they write. If I convert an Access table over to SQLServer I add even
another field as a primary key, usually prefixed with SS (Gasp!).

I keep Jamie's advice in the back of my mind, about how enforcing
constraints at the table level is better than enforcing them through
code, but where I work, no one is going to access the table data using
anything other than Access so I am able to take the high road at my
leisure. Maybe my coding practice just needs to catch up with my
philosophy. Theoretically, the idea of using natural keys is more
intellectually satisfying, but for now the lure of simpler joins is
winning out. Lately, I've increased the amount of normalization in one
of my databases and the joins got even more complicated, adding about a
line or so in the SQL view in Access for every new query using those
tables. Queries involving many to many relationships often add
additional tables later and highlight the need to keep joins as simple
as possible.

James A. Fortune
(e-mail address removed)

 

[Bob Badour]

Whenever I have multiple key fields, natural or not, I create an
AutoNumber PK for pragmatic reasons. The main reason is that it makes
it easier to create the joins. The theorists are champions at joining
tables and don't have to be concerned with the complexity of the SQL
they write.

Anyone who writes SQL is a theorist regardless whether the person is
smart enough and educated enough to realise it.


If I convert an Access table over to SQLServer I add even

another field as a primary key, usually prefixed with SS (Gasp!).

There is no stopping the invincibly ignorant. -- DT

I keep Jamie's advice in the back of my mind, about how enforcing
constraints at the table level is better than enforcing them through
code, but where I work, no one is going to access the table data using
anything other than Access so I am able to take the high road at my
leisure.

Uh huh. ::rolls eyes::


Maybe my coding practice just needs to catch up with my

philosophy. Theoretically, the idea of using natural keys is more
intellectually satisfying, but for now the lure of simpler joins is
winning out.

You sound like one of these programmers who writes shitty, buggy code to
avoid a few keystrokes.


Lately, I've increased the amount of normalization in one

of my databases and the joins got even more complicated,

Define "amount of normalization". Do you even have a clue what it is?


adding about a

line or so in the SQL view in Access for every new query using those
tables. Queries involving many to many relationships often add
additional tables later and highlight the need to keep joins as simple
as possible.

An intelligent, informed person would always follow the design principle
of "As simple as possible and no simpler." I don't see what that has to
do with joining tables (or relations for that matter) other than perhaps
the ability to even recognize complexity when one sees it.

Following the above design principle, one would not needlessly increase
complexity with additional arbitrary columns and keys.

 

[David W. Fenton]

:

But then for all I know, MS Access might allow duplicates
anyhow....

Any database engine can have the schema defined in a way that will
allow duplicates. This is not an issue of the db engine in use, but
of the skill of the operator in association with the requirements of
the application the database is used with.

 

[David W. Fenton]

:

I just think you're all damn brave for using Access in the first
place.

That just goes to show you haven't got a clue about what MS Access
actually is.

Typical.

 

[Larry Daugherty]

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

Until this thread, I thought that I might be the only person in the
world who reflexively entered an Autonumber PK into *every* table I
design. My reasons for using Autonumber PKs is practical and based on
experience.

As for using them in junction tables, I once stopped and thought about
it. I couldn't see a risk in either using them or not using them.
Since Autonumber PKs had saved me countless hours of re-work in normal
tables, my bias was and is to use them. I don't revisit that decision
every time I create a junction table, I just do it. Apparently, Tony
Toews went through similar reasoning. I didn't ask anyone's advice or
permission. It is not necessary that anyone else be persuaded to use
any method I adopt.

It has never caused me the least problem. There has been no
revelation in this thread that would cause me to even revisit the
decision.

In the one reported instance of a "problem", the Autonumber PK along
with the two FKs wasn't the issue. That configuration simply spewed
different erroneous data from the erroneous data that would be spewed
by sticking with natural keys alone. As reported, the problem in that
case was erroneous data entry not a flawed schema.

My advice to OP and to everyone else is to use which ever mode seems
best to him or her.

HTH