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 |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|