 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / August 2004Discussion: What are the advantages/disadvantages to combination k
Hi everyone, I'm interested in hearing the opinions of people who use (or choose not to use) combination keys in their table structure. What do you think the advantages and disadvantages of using combination keys are? If I were to have these tables: tblMainTopic MainID tblSubTopic SubID Then I have two options for relating the two above tables in a junction table. Option I: tblManyToMany MainID { These two foreign keys SubID { are joined as a combination key Option II: tblManyToMany m2mID 'auto-number primary MainID 'foreign key SubID 'foreign key Thanks to all who take the time to post their opinions.  Signaturerpw rpw, In my opinion, the only reason for ever using an AutoNumber field for a primary key is when there is not an easily identifiable natural key. In a junction table the 2 primary keys from the foreign tables are a perfect natural key. Therefore, it seems to me, that Option II is a bit of over kill, especially since you would want to put a Unique Index on the combined foreign keys anyway. SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm > Hi everyone, > [quoted text clipped - 26 lines] > > Thanks to all who take the time to post their opinions. Hi Lynn, Thank you for responding. Sorry for asking pea-brain questions, but does the junction table having child tables or the number of fields making up the combination PK have any influence on the decision? > rpw, > In my opinion, the only reason for ever using an AutoNumber field for a [quoted text clipped - 35 lines] > > > > Thanks to all who take the time to post their opinions. personally, i normally don't use a combination primary key in any table that is the "parent" in a parent/child relationship with another table, because i don't like multi-field foreign keys. but that's just me. (be gentle, Lynn! <g>) > Hi Lynn, > [quoted text clipped - 41 lines] > > > > > > Thanks to all who take the time to post their opinions. I use surrogate AutoNumber PK because when I need to identify a Record in code, I can use a single-field PK rather than multi-Field PK coding (a bit more messy as I have to identify by 2 or more values rather than one). But like Lynn wrote, I still have to set Unique Index on the ForeignKeys, anyway. Perhaps, Lynn's system is more correct for the database purists, though. SignatureHTH Van T. Dinh MVP (Access) > personally, i normally don't use a combination primary key in any table that > is the "parent" in a parent/child relationship with another table, because i > don't like multi-field foreign keys. but that's just me. (be gentle, Lynn! > <g>) Thank you tina and Van for jumping in. So far I've can only see that there are only two advantages to the combo PK. The first is saving field space (fields are expensive, records are cheap). But that savings is lost as soon as there is a child table. The second is the cost of indexing the combined fields. I understand that there is a limit to the number of indexes? Is the speed of record retrieval faster with a single field PK than combined/indexed fields? Are there any more advantages to a combo PK? The advantages to the single PK are easier parent/child relation structure (fewer fields to duplicate and drag in the relationship window) and less typing (chance for error?) when writing code or SQL involving the PK. Are there other advantages? Are there any disadvantages to either method? Now that I think about it a little more, if you had a list or combo box used to select a record from a table that has a combo PK, can you set multiple columns as the bound column? > I use surrogate AutoNumber PK because when I need to identify a Record in > code, I can use a single-field PK rather than multi-Field PK coding (a bit [quoted text clipped - 10 lines] > > don't like multi-field foreign keys. but that's just me. (be gentle, Lynn! > > <g>) > I use surrogate AutoNumber PK because when I need to identify a Record in > code, I can use a single-field PK rather than multi-Field PK coding (a bit > more messy as I have to identify by 2 or more values rather than one). Definitely on of the nice use of AutoNumber but so many people get trapped into thinking that it provides a way to avoid redundant data that they skip the next step. Perhaps, the AutoNumber has just been oversold and over used. > But like Lynn wrote, I still have to set Unique Index on the ForeignKeys, > anyway. Perhaps, Lynn's system is more correct for the database purists, > though. Definitely a purist here. <g> SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Hi Tina! Ok, I'll go easy on you -- this time! A single field primary key is nice for creating relationships between tables but, in my opinion, unnecessary in the case of a junction table, as described by rpw. Basically, each field in the table is the child of only one parent table. SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm > personally, i normally don't use a combination primary key in any table that > is the "parent" in a parent/child relationship with another table, because i [quoted text clipped - 51 lines] > > > > > > > > Thanks to all who take the time to post their opinions. true. when using the foreign keys from the parent tables as the combo primary key of the child table, no problem. but in the few instances when i've used that child table in turn as the parent of another table, i've opted for a unique index on the combined foreign key fields where appropriate, and a surrogate primary key. <cringes and covers head with arms> > Hi Tina! > Ok, I'll go easy on you -- this time! A single field primary key is nice for [quoted text clipped - 65 lines] > > > > > > > > > > Thanks to all who take the time to post their opinions. No need to duck, young lady. I would most likely do the same thing. I just cringe at the thought of using a surrogate primary key as the sole means of uniquely identifying a record. SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm > true. when using the foreign keys from the parent tables as the combo > primary key of the child table, no problem. but in the few instances when [quoted text clipped - 80 lines] > > > > > > > > > > > > Thanks to all who take the time to post their opinions. thanks, Lynn! maybe continued association will cause some of those good "purist" habits to rub off on me. <bows and smiles, surreptitiously wiping brow> > No need to duck, young lady. I would most likely do the same thing. I just > cringe at the thought of using a surrogate primary key as the sole means of [quoted text clipped - 88 lines] > > > > > > > > > > > > > > Thanks to all who take the time to post their opinions. You're most welcome, Tina! Keep up the good work and you'll get it right yet...<g> <bows and smiles back, sees the surreptitious brow wiping...g> SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm > thanks, Lynn! maybe continued association will cause some of those good > "purist" habits to rub off on me. <bows and smiles, surreptitiously wiping [quoted text clipped - 101 lines] > > > > > > > > > > > > > > > > Thanks to all who take the time to post their opinions. rpw, Yes, the number of fields could influence your decision and your database's performance SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm > Hi Lynn, > [quoted text clipped - 41 lines] > > > > > > Thanks to all who take the time to post their opinions. I have just finished reading another thread "How to ID a record" in which Ken Snell refers to the issue of 'natural' vs 'surrogate' keys as being a great debate and there being much to find about the debate by googling. After reading that thread I realized that my question was basically the same, merely phrased less eloquently. So I'd like to apologize for asking people to re-visit a common issue. And, I'd like to thank Lynn Trapp, tina, and Van T. Dinh for adding their comments to this thread. I now have a better understanding of the issue and will continue to use surrogate keys instead of natural combination keys, my reason being primarily because of the easier coding. Thanks again for participating.. :-) > So I'd like to apologize for asking > people to re-visit a common issue. Truly nothing to apologize for, rpw. It's good to revisit this question from time to time. SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.