Re: Separate PK in Jxn Tbl?

"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 Received on Tue Jan 29 2008 - 03:30:18 CET