Re: Separate PK in Jxn Tbl?
From: James A. Fortune <MPAPoster_at_FortuneJames.com>
Date: Tue, 29 Jan 2008 02:59:40 -0500
Message-ID: <eFts00kYIHA.4880_at_TK2MSFTNGP03.phx.gbl>
>>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
>>MPAPoster_at_FortuneJames.com
>>
Date: Tue, 29 Jan 2008 02:59:40 -0500
Message-ID: <eFts00kYIHA.4880_at_TK2MSFTNGP03.phx.gbl>
Neil wrote:
> "James A. Fortune" <MPAPoster_at_FortuneJames.com> wrote in message > news:uLMF40tXIHA.1208_at_TK2MSFTNGP03.phx.gbl... >
>>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
>>MPAPoster_at_FortuneJames.com
>>
> > > I tend to do the same as you: I tend to put autonumber primary key fields in > place where a multi-field PK will do, for the same reasons: i like to be > able to refer to a single key. However, when it comes to junction tables, it > just seems pointless. Referring to two fields instead of one isn't that big > of a deal; and a table made up of two foreign keys is very clean -- adding > an additional key on top of that seems useless and added baggage. > > Larry posted that there are times when an autonumber PK is needed, such as > when using the junction talbe in a combo box, and I would agree with him > there. So in a few cases it might serve a purpose (even though it's not > absolutely necessary there either). But for the most part, it seems > unnecessary. > > Neil
James A. Fortune
MPAPoster_at_FortuneJames.com
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- Received on Tue Jan 29 2008 - 08:59:40 CET