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?
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! |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
Separate PK in Jxn Tbl?
"Roy Hann" wrote
Actually I got the kids to buy it on the way home from school. :-) That could have happened where I grew up -- it was a dry county, but liquor was available from bootleggers and a few people who ran their own stills. |
#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?
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!
In the US, you get a UPC/EAN code and then a batch number with booze. The closest thing to a serial number is your cash register receipt. Now if someone will explain to me why we have expiration dates on salt and bottled water to me because I don't get it. |
#9
|
|||
|
|||
Separate PK in Jxn Tbl?
"-CELKO-" wrote in message ... 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! In the US, you get a UPC/EAN code and then a batch number with booze. The closest thing to a serial number is your cash register receipt. This is the "cat food problem" in a nutshell. (Or, in this discussion, the "gin bottle" problem.) The problem has been known as the "cat food" problem for years and years. Items on a cash register tape represent entities with no identity at the individual level, only at the batch or product level. You therefore can't tell whether two bottles of gin were checked out, or whether one bottle was scanned twice. |
#10
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|