View Single Post
  #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