Re: Multiple Many-To-Many Tables

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: TC (a_at_b.c.d)
Date: 05/15/04 

Date: Sat, 15 May 2004 12:17:52 +0930

Thanks Tom. Good luck with your quest!

TC

"Tom" <test@home.com> wrote in message
news:#RWkzCaOEHA.3596@tk2msftngp13.phx.gbl...
> TC,
>
> anyhow, I appreciate your feedback so far. I'll go ahead and post
another
> thread in hope to get more advice.
>
> --
> Thanks,
> Tom
>
>
> "TC" <no@email.here> wrote in message
> news:40a451d9$1_2@news.chariot.net.au...
> > Yes, you got me spot-on :-)
> >
> > I can only get on the web once a day, & it gets difficult towards
> week-ends,
> > cos I have other obligations.
> >
> > Cheers,
> > TC
> >
> >
> > "rpw" <anonymous@discussions.microsoft.com> wrote in message
> > news:DDB2566D-8DB2-4798-BEE5-AFEB65743906@microsoft.com...
> > > Hi Tom,
> > >
> > > it's been a while since TC answered, so I'll jump in here Comments
> > in-line...
> > >
> > > Not sure if I entirely understand your schema.
> > >
> > >
> > > I got the 3 tables:
> > > tblPersonClub is the junction which has 2 Fields (Long Integer)
> that
> > are
> > > linked to the primary keys of the other 2 tables: tblPerson &
> tblClub
> > >
> > > So here are my questions:
> > > 1. What did you mean by "composite"?
> > >
> > > Instead of one field of the table being designated as the PK, you can
> > select multiple fields and designate the combination as a PK (hence
> > "composite" ) It is difficult to illustrate here, but the intent is
that
> > two or three fields are grouped together as a "composite primary key"
Try
> > looking at TC's original post again with that in mind.
> > >
> > > 2. What is the "DateRange". I assume it's a field, right?
> > >
> > > I believe he meant DateRange as a table (maybe tblDateRange is better)
> and
> > the sub-listings are the fields.
> > >
> > > 3. ClubID is the primary but DateFrom = "key"... not sure what
you
> > mean by
> > > that?
> > >
> > > See the answer to number one above.
> > >
> > > 4. Also, I thought I would end up with 6 tables. 2 pairs of 2
> > tables
> > > linked via their junction tables. And then link the 2 junction
> > tables to
> > > one another... I'm probably wrong about this, right?
> > >
> > > TC's four table sample and explanation should be sufficient to
> accomplish
> > what you asked for - stringing multiple m:m relationships together.
Look
> at
> > his tables/relations again:
> > >
> > > tblPerson (has 1:m to tblPersonClub)
> > > tblClub (has 1:m to tblPersonClub)
> > > tblPersonClub (is the normal junction table structure)
> > > tblDateRange (has 1:m to tblPersonClub)
> > >
> > > In your first post you said:
> > > >> For instance, I may have members that are assigned to many
> > projects (and
> > > >> each project many members). But I also have boards that have
> > multiple
> > > >> priorities and multiple priorities are linked to multiple
> > organizations.
> > >
> > > For what you state specifically, you could have these tables:
> > >
> > > tblMembers
> > > tblProjects
> > > tblMemberProjects
> > >
> > > and
> > >
> > > tblBoards
> > > tblOrganizations
> > > tblBoardOrganization (or tblPriorities - this is your junction table)
> > >
> > > Do you also want m:m Members to Boards, m:m Members to Organization,
and
> > m:m Members to Priorities? (you didn't say that's what you wanted, so
I'm
> > just guessing here) Do Projects have Priorities too? Knowing what you
> > know, what do you think you would need to handle the m:m relationships
> > listed here?
> > >
> > > hope this helps you
> > >
> > > rpw
> > >
> > >
> > > Thanks for any additional info,
> > > Tom
> > >
> > >
> > >
> > >
> > > "TC" <no@email.here> wrote in message
> > > news:40a30331$1_2@news.chariot.net.au...
> > > >> "Tom" <bockREMOVETHIS@sigmongroup.com> wrote in message
> > > > news:eCIENmJOEHA.128@TK2MSFTNGP12.phx.gbl...
> > > >> Hello,
> > > >>>> I am familiar with using a junction table for a
"many-to-many"
> > > > relationship.
> > > >>>> However, I'm not exactly sure how I could string multiple
> > "many-to-many"
> > > > or
> > > >> junction tables into an architecture.
> > > >>>> Does anyone have a suggestion?
> > > >> Here are two examples.
> > > >> 1. If A is many-to-many (m:m) with B, you need a junction
table
> AB
> > (or
> > > > whatever) - as you know. Similarly, if some other table C is
m:m
> to
> > D, you
> > > > need another junction table CD. Ditto for as many other tables
&
> > junction
> > > > pairs that are required.
> > > >> 2. Say you have Person, Club, and PersonClub. (The latter is
the
> > junction
> > > > table to allow a person to belong to many clubs, and a club to
> have
> > many
> > > > persons.) Say you need to record each consecutive period that a
> > person was
> > > a
> > > > member of each club. (Eg. 1990-1993, 1998-2001 etc). In this
> case,
> > for
> > > each
> > > > one entry in the PersonClub table, there are many entries in a
> > table of
> > > > date-ranges:
> > > >> tblPersonClub
> > > > PersonID ( composite )
> > > > ClubID ( primary key )
> > > >> DateRange
> > > > PersonID ( composite )
> > > > ClubID ( primary )
> > > > DateFrom ( key )
> > > > DateTo
> > > >> In the latter example:
> > > >> - Person : Club is m:m ) the normal
> > > > - Person : PersonClub is 1:m ) junction table
> > > > - Club : PersonClub is 1:m ) structure
> > > >> - PersonClub : DateRange is 1:m
> > > >> Do those help?
> > > >>>>> For instance, I may have members that are assigned to many
> > projects (and
> > > >> each project many members). But I also have boards that have
> > multiple
> > > >> priorities and multiple priorities are linked to multiple
> > organizations.
> > > >>>> Currently, I'm myself trying to figure out the relationships
> > between all
> > > > the
> > > >> entities. Again, knowing how I could use (with a generic
> > example)
> > > > multiple
> > > >> junction tables would be great advice.
> > > >>>> Thanks,
> > > >> Tom
> > > >>>>>>>>
> >
> >
>
>

Relevant Pages

  • Re: Can you decrypt this?
    ... Just confessing below a feedback during the junction is too ... british for Selma to reply it. ...
    (sci.crypt)
  • Re: Multiple Many-To-Many Tables
    ... > linked via their junction tables. ... what you asked for - stringing multiple m:m relationships together. ... just guessing here) Do Projects have Priorities too? ... >> table to allow a person to belong to many clubs, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Multiple Many-To-Many Tables
    ... linked via their junction tables. ... > what you asked for - stringing multiple m:m relationships together. ... >> each project many members). ... >> priorities and multiple priorities are linked to multiple ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Form that also updates the intermediary table?
    ... Administration is at both sites. ... > I doubt that you need a junction table between Site and Location. ... > as one site containing multiple locations. ... rather than allenbrowne at mvps dot org. ...
    (microsoft.public.access.queries)
  • Re: microcontroller advice? how to go about this...
    ... If it is a one off, then I 'd go with multiple 12 bit ADCs with an SPI ... USB is not so easy to work with. ... > wondering if someone could give some advice on what the best way to go ...
    (comp.arch.embedded)