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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|