Re: Discussion: What are the advantages/disadvantages to combinati

From: Lynn Trapp (ltrappNoSpam_at_ltcomputerdesigns.com)
Date: 08/30/04 

Date: Mon, 30 Aug 2004 11:06:57 -0500

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. 

-- 
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
"tina" <nospam@address.com> wrote in message
news:HWTXc.524680$Gx4.404542@bgtnsc04-news.ops.worldnet.att.net...
> 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>)
>
>
> "rpw" <rpw@discussions.microsoft.com> wrote in message
> news:CA6308B7-F751-44E2-A4CC-C7710B58A248@microsoft.com...
> > 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?
> >
> > "Lynn Trapp" wrote:
> >
> > > 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.
> > >
> > > -- 
> > > Lynn Trapp
> > > MS Access MVP
> > > www.ltcomputerdesigns.com
> > > Access Security: www.ltcomputerdesigns.com/Security.htm
> > >
> > >
> > > "rpw" <rpw@discussions.microsoft.com> wrote in message
> > > news:27B2F3B8-9DD0-4836-9FB6-D80DA82C76AD@microsoft.com...
> > > > 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.
> > > >
> > > > -- 
> > > > rpw
> > >
> > >
> > >
>
>

Relevant Pages
Loading