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
  #1  
Old January 22nd, 2008, 09:26 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Neil
external usenet poster
 
Posts: 311
Default Separate PK in Jxn Tbl?

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


  #2  
Old January 22nd, 2008, 09:43 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Phil Stanton
external usenet poster
 
Posts: 17
Default Separate PK in Jxn Tbl?

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



  #3  
Old January 22nd, 2008, 10:02 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Roy Hann
external usenet poster
 
Posts: 25
Default Separate PK in Jxn Tbl?

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


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


  #4  
Old January 22nd, 2008, 10:23 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?


"Neil" wrote in message
. ..

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?




  #5  
Old January 22nd, 2008, 10:35 PM posted to comp.databases.ms-access,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Separate PK in Jxn Tbl?

On Tue, 22 Jan 2008 20:26:42 GMT, "Neil" wrote:

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]
  #6  
Old January 22nd, 2008, 11:01 PM 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?

Roy Hann wrote:

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


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!
  #7  
Old January 22nd, 2008, 11:01 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Phil Stanton
external usenet poster
 
Posts: 17
Default Separate PK in Jxn Tbl?

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


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



  #8  
Old January 22nd, 2008, 11:05 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 22, 8:43 pm, "Phil Stanton" wrote:
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.


So, say that book was "war and peace" and it was john who took it out
on loan, then you would be recording the propositions:

John has on loan the book "War and Peace" and
John has on loan the book "War and peace"

Genius This is actually a relatively serious design flaw, as you
are trying to state the exact same fact twice. If the two books that
John took out really are indiscernible, then the proposition we have
actually recognized is:

John has on loan 2 copies of the book "War and Peace"



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


  #9  
Old January 23rd, 2008, 01:18 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Roy Hann
external usenet poster
 
Posts: 25
Default Separate PK in Jxn Tbl?

"Bob Badour" wrote in message
...

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


  #10  
Old January 23rd, 2008, 01:45 AM posted to comp.databases.ms-access,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Neil
external usenet poster
 
Posts: 311
Default Separate PK in Jxn Tbl?


"John W. Vinson" wrote in message
...
On Tue, 22 Jan 2008 20:26:42 GMT, "Neil" wrote:

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]



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


 




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 11:02 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.