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
  #31  
Old January 24th, 2008, 02:47 PM 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, 11:18*am, Jamie Collins wrote:
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".


I imagine he meant "antagonist" to be honest. But then, only seeing
this thread from c.d.t and not knowing the ms-access glitterati, I
have no idea as to whether he would be correct in such an assertion.

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


Jamie.

--


  #32  
Old January 24th, 2008, 03:00 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, 1:26 pm, lyle wrote:
Thank you. I've learned that Tony and I share two rules.


It was an analogy and meant to be taken with a pinch of salt ;-)

Jamie.

--

  #33  
Old January 24th, 2008, 07:43 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
David Cressey[_2_]
external usenet poster
 
Posts: 50
Default Separate PK in Jxn Tbl?


"JOG" wrote in message
...
On Jan 24, 7:03 am, "Brian Selzer" wrote:
(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.


  #34  
Old January 24th, 2008, 09:36 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, 6:43 pm, "David Cressey" wrote:
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.


Please post you findings here

Jamie.

--

  #35  
Old January 24th, 2008, 09:53 PM 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?

JOG wrote:

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/
  #36  
Old January 25th, 2008, 12:00 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
James A. Fortune
external usenet poster
 
Posts: 903
Default Separate PK in Jxn Tbl?

Neil wrote:
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

  #37  
Old January 25th, 2008, 01:44 AM posted to comp.databases.ms-access,comp.databases.theory,,microsoft.public.access,microsoft.public.access.tablesdbdesign,,microsoft.public.sqlserver
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Separate PK in Jxn Tbl?

JOG wrote in

:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #38  
Old January 25th, 2008, 01:45 AM posted to comp.databases.ms-access,comp.databases.theory,,microsoft.public.access,microsoft.public.access.tablesdbdesign,,microsoft.public.sqlserver
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Separate PK in Jxn Tbl?

JOG wrote in

:

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #39  
Old January 25th, 2008, 02:04 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Larry Daugherty
external usenet poster
 
Posts: 1,012
Default Separate PK in Jxn Tbl?

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

"Phil Stanton" wrote in message
...
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

"Neil" wrote in message
. ..
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





  #40  
Old January 25th, 2008, 02:08 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Bob Badour
external usenet poster
 
Posts: 38
Default Separate PK in Jxn Tbl?

Larry Daugherty wrote:
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.


Bull****! Ignorance, stupidity and laziness are not practical.

[snip]
 




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