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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: tina (nospam_at_address.com)
Date: 08/30/04 

Date: Mon, 30 Aug 2004 18:50:25 GMT

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>

"Lynn Trapp" <ltrappNoSpam@ltcomputerdesigns.com> wrote in message
news:uLkThtqjEHA.3848@tk2msftngp13.phx.gbl...
> 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

  • Re: Connecting to the Same Table Twice
    ... RelationshipID (primary key) ... RelationshipName (parent, guardian, authorized pick-up, emergency contact, ... you might put spouse names into a separate table linked to this table). ... for each link between a child record and a caregiver record, ...
    (microsoft.public.access.gettingstarted)
  • Re: Connecting to the Same Table Twice
    ... > RelationshipID (primary key) ... > RelationshipName (parent, guardian, authorized pick-up, emergency contact, ... > you might put spouse names into a separate table linked to this table). ... > for each link between a child record and a caregiver record, ...
    (microsoft.public.access.gettingstarted)
  • Re: MS Access
    ... primary key has some blank, ... We can call this our parent or so called "main" table. ... you must declare a new column in this child table. ... In the above you can tell the customer with an id of 2 has a favorite color ...
    (microsoft.public.access.gettingstarted)
  • Re: Data Normalization
    ... CustomerID foreign key. ... of the database whether an SSN would be a good candidate for a primary key. ... applicable that relates that given record to it's parent. ... Create the relationship between the child table and my new parent ...
    (microsoft.public.access.queries)
  • Re: Trigger/Foreign Key limitation for real?
    ... field in it to match the parent primary key. ... I tried to create an instead of update trigger on the parent table. ... I tried to create an instead of update trigger on the child table. ...
    (microsoft.public.sqlserver.programming)