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, 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!
  #5  
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


  #6  
Old January 23rd, 2008, 04:03 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Larry Linson
external usenet poster
 
Posts: 3,112
Default 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  
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 27th, 2008, 09:51 PM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
-CELKO-
external usenet poster
 
Posts: 26
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!

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  
Old January 28th, 2008, 03:07 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?


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


 




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 01:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.