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 |
#31
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 24, 11:18*am, Jamie Collins wrote:
On Jan 24, 2:14 am, "Larry Linson" wrote: * A pragmatic person would at least listen to the arguments * of theorists before dismissing them. In this case, of course, *it is immaterial, because Tony is correct that Jamie is not a pragmatist, at least not demonstrably so in his posts that I have observed. Care to back up your claim with some evidence? No, you are mistaken. I *frequently* (to the point of boring people, I'm sure) back up my assertions with Jet SQL code, VBA demos, links to Microsoft articles and the Access Help, and stories from the field. This thread is a good example of that. Is there a category of "argumentist"? I think the word you are looking for is "critic". I imagine he meant "antagonist" to be honest. But then, only seeing this thread from c.d.t and not knowing the ms-access glitterati, I have no idea as to whether he would be correct in such an assertion. I just think you're all damn brave for using Access in the first place. Jamie. -- |
#32
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 24, 1:26 pm, lyle wrote:
Thank you. I've learned that Tony and I share two rules. It was an analogy and meant to be taken with a pinch of salt ;-) Jamie. -- |
#33
|
|||
|
|||
Separate PK in Jxn Tbl?
"JOG" wrote in message ... On Jan 24, 7:03 am, "Brian Selzer" wrote: (quote) An actual example I experience springs to mind - I have witnessed a database where student projects were recorded via a schema of Project Partners:{id:autonumber, RoleAerson, RoleBerson}, with PK(id). None of the partnerships were aware of any "id" in the real world, and simply submitted their partnership choices on paper to admin. A clerical error resulted in 2/3 of the data being entered twice, which left a lot of people flapping about the number of markers required until the error was found. If the schema had used the natural {RoleA, RoleB} key there would have been no issue. But then for all I know, MS Access might allow duplicates anyhow.... (/quote) I have begun to mess around with MS Access, as a retirement hobby, after working professionally with some SQL DBMS products. AFAIK, MS Access enforces the no duplicates rule, and the no missing data rule, for every PK that's declared. There are other ways to express these constraints, but the easiest way to get them is to declare a PK. For that reason, I prefer not to create a new ID with an autonumber for any junction table. Tony have have his reasons for going the other way. Until I know what they are, I remain unpersuaded. The fact tables in a star schema have the same property as a junction table, except that the number of FKs that are components of the PK may be larger than 2, and generally is larger. I've just started messing with star schemas in MS Access. It's too soon for me to offer even a guess as to whether this is a smart idea or a stupid idea. All I know is that it will provide some cheap amusement for my retirement. |
#34
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 24, 6:43 pm, "David Cressey" wrote:
I've just started messing with star schemas in MS Access. It's too soon for me to offer even a guess as to whether this is a smart idea or a stupid idea. All I know is that it will provide some cheap amusement for my retirement. Please post you findings here Jamie. -- |
#35
|
|||
|
|||
Separate PK in Jxn Tbl?
JOG wrote:
I just think you're all damn brave for using Access in the first place. Why? It works and works well. If you have too many users or remote users bolt on a SQL Server backend. Now you can have thousands of users. 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/ |
#36
|
|||
|
|||
Separate PK in Jxn Tbl?
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 Whenever I have multiple key fields, natural or not, I create an AutoNumber PK for pragmatic reasons. The main reason is that it makes it easier to create the joins. The theorists are champions at joining tables and don't have to be concerned with the complexity of the SQL they write. If I convert an Access table over to SQLServer I add even another field as a primary key, usually prefixed with SS (Gasp!). I keep Jamie's advice in the back of my mind, about how enforcing constraints at the table level is better than enforcing them through code, but where I work, no one is going to access the table data using anything other than Access so I am able to take the high road at my leisure. Maybe my coding practice just needs to catch up with my philosophy. Theoretically, the idea of using natural keys is more intellectually satisfying, but for now the lure of simpler joins is winning out. Lately, I've increased the amount of normalization in one of my databases and the joins got even more complicated, adding about a line or so in the SQL view in Access for every new query using those tables. Queries involving many to many relationships often add additional tables later and highlight the need to keep joins as simple as possible. James A. Fortune |
#37
|
|||
|
|||
Separate PK in Jxn Tbl?
JOG wrote in
: But then for all I know, MS Access might allow duplicates anyhow.... Any database engine can have the schema defined in a way that will allow duplicates. This is not an issue of the db engine in use, but of the skill of the operator in association with the requirements of the application the database is used with. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#38
|
|||
|
|||
Separate PK in Jxn Tbl?
JOG wrote in
: I just think you're all damn brave for using Access in the first place. That just goes to show you haven't got a clue about what MS Access actually is. Typical. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#39
|
|||
|
|||
Separate PK in Jxn Tbl?
This boil up is a variation of the "Autonumber vs. Natural Key"
religious wars that sweep the Access groups on even numbered(?) years. In the meantime OP is probably trying to hide the matches with which he started the fires.... Until this thread, I thought that I might be the only person in the world who reflexively entered an Autonumber PK into *every* table I design. My reasons for using Autonumber PKs is practical and based on experience. As for using them in junction tables, I once stopped and thought about it. I couldn't see a risk in either using them or not using them. Since Autonumber PKs had saved me countless hours of re-work in normal tables, my bias was and is to use them. I don't revisit that decision every time I create a junction table, I just do it. Apparently, Tony Toews went through similar reasoning. I didn't ask anyone's advice or permission. It is not necessary that anyone else be persuaded to use any method I adopt. It has never caused me the least problem. There has been no revelation in this thread that would cause me to even revisit the decision. In the one reported instance of a "problem", the Autonumber PK along with the two FKs wasn't the issue. That configuration simply spewed different erroneous data from the erroneous data that would be spewed by sticking with natural keys alone. As reported, the problem in that case was erroneous data entry not a flawed schema. My advice to OP and to everyone else is to use which ever mode seems best to him or her. HTH -- -Larry- -- "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. 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 |
#40
|
|||
|
|||
Separate PK in Jxn Tbl?
Larry Daugherty wrote:
This boil up is a variation of the "Autonumber vs. Natural Key" religious wars that sweep the Access groups on even numbered(?) years. In the meantime OP is probably trying to hide the matches with which he started the fires.... Until this thread, I thought that I might be the only person in the world who reflexively entered an Autonumber PK into *every* table I design. My reasons for using Autonumber PKs is practical and based on experience. Bull****! Ignorance, stupidity and laziness are not practical. [snip] |
Thread Tools | |
Display Modes | |
|
|