Re: Separate PK in Jxn Tbl?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Thu, 24 Jan 2008 19:40:21 -0400
Message-ID: <479921e9$0$4066$9a566e8b_at_news.aliant.net>


James A. Fortune wrote:

> 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.

Anyone who writes SQL is a theorist regardless whether the person is smart enough and educated enough to realise it.

If I convert an Access table over to SQLServer I add even
> another field as a primary key, usually prefixed with SS (Gasp!).

There is no stopping the invincibly ignorant. -- DT

> 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.

Uh huh. ::rolls eyes::

   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.

You sound like one of these programmers who writes shitty, buggy code to avoid a few keystrokes.

   Lately, I've increased the amount of normalization in one
> of my databases and the joins got even more complicated,

Define "amount of normalization". Do you even have a clue what it is?

  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.

An intelligent, informed person would always follow the design principle of "As simple as possible and no simpler." I don't see what that has to do with joining tables (or relations for that matter) other than perhaps the ability to even recognize complexity when one sees it.

Following the above design principle, one would not needlessly increase complexity with additional arbitrary columns and keys. Received on Fri Jan 25 2008 - 00:40:21 CET

Original text of this message