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
  #11  
Old January 23rd, 2008, 02:41 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Separate PK in Jxn Tbl?

"Neil" wrote:

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?


I always use an autonumber PK and a uniqui index set on the two FK fields.

Why? No particular good reason. One of my database rules is that all tables have
an autonumber primary key. It's also slightly easier to delete the record in code.

Now if I was to have a child table from the junction table then I would absolutely
use a autonumber primary key for ease of use when designing queries, forms and
reports.

The theorists will argue. I don't care.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
  #12  
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.


  #13  
Old January 23rd, 2008, 10:05 AM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Separate PK in Jxn Tbl?

On Jan 23, 1:41 am, "Tony Toews [MVP]" wrote:
I always use an autonumber PK and a uniqui index set on the two FK fields.

Why? No particular good reason. One of my database rules is that all tables have
an autonumber primary key.

The theorists will argue. I don't care.


Would you care for a pragmatist to offer a counter argument?

I never seek to add columns where an attribute does not exist in the
reality being modelled; sometimes I do end up adding something
'artificial' but only when there is a "good data modelling" reason for
doing so.

In the scenario described, there is a two-column "all key" table which
means it is in 5NF. From a "data modelling" point of view I can't see
a case for adding an 'artifical key'.

Jamie.

--

  #14  
Old January 23rd, 2008, 03:20 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 23, 9:05 am, Jamie Collins wrote:
On Jan 23, 1:41 am, "Tony Toews [MVP]" wrote:

I always use an autonumber PK and a uniqui index set on the two FK fields.


Why? No particular good reason. One of my database rules is that all tables have
an autonumber primary key.


The theorists will argue. I don't care.


Would you care for a pragmatist to offer a counter argument?

I never seek to add columns where an attribute does not exist in the
reality being modelled; sometimes I do end up adding something
'artificial' but only when there is a "good data modelling" reason for
doing so.

In the scenario described, there is a two-column "all key" table which
means it is in 5NF. From a "data modelling" point of view I can't see
a case for adding an 'artifical key'.


To the OP, I agree with this point of view. Again from a totally
practical standpoint, it is nonsense adding an artificial key would
not "make it slightly easier to delete records", because when
something changes in the real world, you need that data that made up
the 'original' superkey to know which row to delete anyhow.

I'd ignore anyone who claims to be telling you based on a purely
"practical" /or/ purely theoretical standpoints. Its always important
to be aware of both extremes, and balance them in order to work out
how to best get the job done.


Jamie.

--


  #15  
Old January 23rd, 2008, 03:42 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 23, 2:20 pm, JOG wrote:
On Jan 23, 9:05 am, Jamie Collins wrote:



On Jan 23, 1:41 am, "Tony Toews [MVP]" wrote:


I always use an autonumber PK and a uniqui index set on the two FK fields.


Why? No particular good reason. One of my database rules is that all tables have
an autonumber primary key.


The theorists will argue. I don't care.


Would you care for a pragmatist to offer a counter argument?


I never seek to add columns where an attribute does not exist in the
reality being modelled; sometimes I do end up adding something
'artificial' but only when there is a "good data modelling" reason for
doing so.


In the scenario described, there is a two-column "all key" table which
means it is in 5NF. From a "data modelling" point of view I can't see
a case for adding an 'artifical key'.


To the OP, I agree with this point of view. Again from a totally
practical standpoint, it is nonsense adding an artificial key would
not "make it slightly easier to delete records", because when
something changes in the real world, you need that data that made up
the 'original' superkey to know which row to delete anyhow.


That should have read "from a totally practical standpoint, adding an
artificial key would not 'make it slightly easier to delete
records'".


I'd ignore anyone who claims to be telling you based on a purely
"practical" /or/ purely theoretical standpoints. Its always important
to be aware of both extremes, and balance them in order to work out
how to best get the job done.



Jamie.


--


  #16  
Old January 23rd, 2008, 06:01 PM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Dustin W. Jones
external usenet poster
 
Posts: 2
Default Separate PK in Jxn Tbl?

On Jan 22, 3:26 pm, "Neil" wrote:
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


I've done both. I try to use natural keys where applicable, usually
both PK's as the primary for the junction table.
  #17  
Old January 23rd, 2008, 08:45 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Separate PK in Jxn Tbl?

Jamie Collins wrote:

The theorists will argue. I don't care.


Would you care for a pragmatist to offer a counter argument?


You're no pragmatist.

tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
  #18  
Old January 23rd, 2008, 10:41 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?


"Tony Toews [MVP]" wrote in message
...
"Neil" wrote:

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?


I always use an autonumber PK and a uniqui index set on the two FK fields.

Why? No particular good reason. One of my database rules is that all

tables have
an autonumber primary key. It's also slightly easier to delete the

record in code.

Now if I was to have a child table from the junction table then I would

absolutely
use a autonumber primary key for ease of use when designing queries, forms

and
reports.

The theorists will argue. I don't care.


Sometimes, theory IS practical. (Some would say always). A pragmatic person
would at least listen to the arguments of theorists before dismissing them.



  #19  
Old January 23rd, 2008, 10:59 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?

David Cressey wrote:

"Tony Toews [MVP]" wrote in message
...

"Neil" wrote:


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?


I always use an autonumber PK and a uniqui index set on the two FK fields.

Why? No particular good reason. One of my database rules is that all


tables have

an autonumber primary key. It's also slightly easier to delete the


record in code.

Now if I was to have a child table from the junction table then I would


absolutely

use a autonumber primary key for ease of use when designing queries, forms


and

reports.

The theorists will argue. I don't care.


Sometimes, theory IS practical. (Some would say always). A pragmatic person
would at least listen to the arguments of theorists before dismissing them.


The theorists won't argue. The theorists will simply point out the
inherent stupidity of abdicating thought for simplistic recipes. The
invincibly ignorant won't care. They never do.
  #20  
Old January 24th, 2008, 01:33 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Brian Selzer
external usenet poster
 
Posts: 32
Default Separate PK in Jxn Tbl?


"Tony Toews [MVP]" wrote in message
...
"Neil" wrote:

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?


I always use an autonumber PK and a uniqui index set on the two FK fields.

Why? No particular good reason. One of my database rules is that all
tables have
an autonumber primary key. It's also slightly easier to delete the
record in code.


Only an idiot would have a rule for no particularly good reason. Only an
imbecile would follow such a rule. A strong argument can be made for using
autonumber primary keys--especially if the target DBMS doesn't support FOR
EACH ROW triggers--but to just blythely add them for no particularly good
reason is a recipe for disaster. A clear understanding of how and when they
can be used and why is critical or you run the risk of a corrupt database.

Now if I was to have a child table from the junction table then I would
absolutely
use a autonumber primary key for ease of use when designing queries, forms
and
reports.

The theorists will argue. I don't care.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/



 




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