Separate PK in Jxn Tbl?

[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

 

[Bob Badour]

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.

Bullshit! Ignorance, stupidity and laziness are not practical.

[snip]

 

[Tony Toews [MVP]]

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.

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.

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 Daugherty]

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.

Keep up the good work!

 

[lyle fairfield]

Bullshit! Ignorance, stupidity and laziness are not practical.

If you’re feeling angry because you can’t program or design databases well,
perhaps you could describe your problems here, instead of posting streams
of invective. I’m sure that Tony, James or Larry will be able to simplify
the concepts required and suggest solutions suitable even for someone who
seems so backward and confused.

Hope this helps!

 

[Bob Badour]

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.



Ignorance is bliss.

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.

 

[Tony Toews [MVP]]

I was tempted to simply reply:

"avoiding using Access is just a rule I have. Why? No good reason. The
access fanboys will argue. I don't care"

...but, hey that sort of sentiment would surely just be peurile, and
discourage useful discussion would it not?


Or I could just use a decent database architecture in the first place
(and I am lucky enough to be able to), that can cope with more that 10
users (I mean 255 *cough*). Thats why I salute your dedication in the
face of all that superior db technology. I mean, we all know that the
client really wants to use oracle, and yet its you that stoutly has to
deal with his corner cutting as best you can.

But in many situations why go to the extra effort of Oracle or <cough> SQL Server
required? I seldom create systems that will be used by more than ten people. Using
Access in such an environment is a simpler install and simple updating. No DBA
required and very little IT admin.

I certainly don't mean to upset you....Well okay, maybe there's just a
bit of friendly ribbing in there

<smile>

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

I was tempted to simply reply:

"avoiding using Access is just a rule I have. Why? No good reason. The
access fanboys will argue. I don't care"

...but, hey that sort of sentiment would surely just be peurile, and
discourage useful discussion would it not?

BTW I didn't realize that there were people in the theory newsgroup who actually
wanted to discuss the issues in a reasonable fashion.

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

Ignorance is bliss.

Politeness goes a long way.

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/

 

[Bob Badour]

If you�re feeling angry because you can�t program or design databases well,

Your question suffers the fallacy of many questions. First, I don't feel
angry at all. Second, I can do both very well. Are you feeling angry
because you can't program or design databases well?

perhaps you could describe your problems here, instead of posting streams
of invective. I�m sure that Tony, James or Larry will be able to simplify
the concepts required and suggest solutions suitable even for someone who
seems so backward and confused.

Since they are simpletons, I have no doubt they would simplify the
concepts ... beyond all recognition.

 

[Brian Selzer]

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

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.

Are we feeling a bit abused? Do we need a pity party? All together now,
1...2...3.... There, now, did that make poor little Tony feel any better?

Please don't lump me in with Badour. I rarely use the terms, 'idiot' and
'imbecile,' but (and I think if you'll reexamine it dispassionately, you'll
agree) your original statement in this thread was, in my opinion, pretty
idiotic. Oh, and by the way, you still haven't shared your reasons. What
are you afraid of?

 

[Jamie Collins]

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

Jamie.

--

 

[rkc]

"Clearly the person is an idiot and is better off employed at a 7-11."
http://groups.google.com/group/microsoft.public.access.conversion/msg/163c373772d787d3

"One idiot student once emailed me"
http://groups.google.com/group/microsoft.public.access.modulesdaovba/msg/4d5e152c11a9e0a3

"tell him he's an idiot"
http://groups.google.com/group/microsoft.public.access/msg/31c0abc7b367656d

"This person is an idiot."
http://groups.google.com/group/microsoft.public.access/msg/6fedf72a4680cf25

"But no, some idiot manager at Mickeysoft..."
http://groups.google.com/group/microsoft.public.access.forms/msg/222d3ed992e9a2ec

Jamie.

Oops.

 

[Sylvain Lafontaine]

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

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. But even if you
take out such interfaces out of the equation, the use of a surrogate key for
all tables reveals itself to be advantageous in many database problems. For
example, if you want to add a log of all changes to a table, it's much more
easier to design it if the table use a surrogate key for its primary key
than a natural key or a composite key.

Personally, I stopped using natural keys and composite keys many years ago
and probably that something like half of my problems with the design of
databases have vanished with them. On these occasions when I was called to
work on a problematic database, chances was much higher to see that the
problems were associated with the use of natural keys and/or composite keys
than with the use of a surrogate keys and the solutions were usually much
more complicated to solve in the first case than in the second case.

Also, I've remember some peoples who have done like me and have stopped
using natural and composite keys in favor of the exclusive use of surrogate
keys but I don't remember anyone doing the opposite; ie. going from the use
of surrogate keys to the use of natural and composite keys.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

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.

Word to the wise: 'theorists' hate SQL.

Queries involving many to many relationships often add
additional tables later and highlight the need to keep joins as simple
as possible.

I think I'm with Bob Badour (gulp!) on the issue of complexity,
though: if you think more columns in the ON clause makes a SQL join
more 'complex' then I think you could be looking at things wrong.
Having more characters to type increases the risk of typos? More
columns mean you may omit one in error? The SQL engine may be twice as
slow in handling two columns rather than one? Is it more 'complex' to
split a post address into 'subatomic' columns (address lines from
postal code/zip etc)?

Surely the issue you allude to (I think) is the one that Access
Relationships (as distinct from Jet foreign keys) were invented to
solve? i.e. you pre-define the join columns and 'join type' (inner
join, left outer join or right outer join) and the join clause gets
written as SQL for you when you drop the tables into the Query Builder
thing. I would have thought the 'theorists' would love the fact that
you also create foreign keys in the same Relationships dialog i.e. you
end up with a natural join (not having to explicitly specify the
columns yourself) because one table references the other.

[I tend to be dismissive of tools that write SQL code for me but I
think I should perhaps review my stance e.g. I still write all my SQL
Server procs by hand whereas I have tasked myself to investigate CRUD
generators. But, for the time being, ...] As a SQL coder myself, I
find it more annoying that I have to create multiple joins to get the
'natural key' values, having to discover what the 'artificial key'
columns are in the first place.

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.

Bad luck: I think you might have got way with "reduced the amount of
denormalization" ;-) In this thread I've already broken my personal
rule (!!) about not mentioning normalization [formulated because the
average 'replier' around here thinks "fully normalized" is BCNF, which
they think is 3NF anyhow, and doesn't pay much attention to anomalies
that normalization doesn't address, unless the 'asker' mentions
storing calculations...]

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

..and best to do it in both places! Bear in mind that it's a rule of
thumb i.e. "strict rules modified in practise." Checking something in
the in front end allows you to give timely user feedback and could
save them some keying, not to mention a database roundtrip. Checking
in the database catches anything neglected in the front end by
omission of validation or introduction of bugs. In practice, some
things are better done in one place but not the other: contrast the
validation of the basic pattern of an email address with the
verification that an addressable entity can be contacted at that email
address; I don't think it would be sensible to put the latter test
into a table constraint, even if it were possible.

Jamie.

--

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.

James A. Fortune
(e-mail address removed)