[AccessD] Re: design/development question on representing mtm relationships

Susan,

If you are going to maintain the relationship, you ALWAYS enter the one 
side before the many side.  I do not understand what you mean by unnatural 
when you said that entering the one before the many felt unnatural.  Can I 
have line items for an order without the order itself?  Can I have a home 
address for a person before I have the person?

And many-to-many is different from the one-to-many that you 
described.  Many-to-many requires what is sometimes referred to as a 
resolver table.  The following is an example:

tblParty        tblPartyPhone        tblPhone       tblPhoneType
PartyID         PartyPhoneID         PhoneID        PhoneTypeID
FirstName       PartyID              CountryCode    PhoneTypeDesc
MiddleName      PhoneID              AreaCityCode
LastName        PhoneTypeID          Exchange
                                      PhoneNumber

The table tblPartyPhone resolves the many-to-many between party and 
phone.  And, it allows the phone to play a different role between different 
people by including the phone type.

And, I would use form/subform for all data entry and never allow a record 
to be added to tblPartyPhone without a phone number, a party, and a type of 
phone.  Which by the way, is the business key (i.e. unique index) for the 
resolver table.

Robert

At 12:01 PM 30/06/2004 -0500, you wrote:
>Date: Wed, 30 Jun 2004 08:10:41 -0400
>From: "Susan Harkins" <ssharkins at bellsouth.net>
>Subject: [AccessD] design/development question on representing mtm
>         relationships
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID:
>         <20040630121038.UJWL1779.imf22aec.mail.bellsouth.net at SUSANONE>
>Content-Type: text/plain;       charset="us-ascii"
>
>During a training session yesterday I had someone ask me what the easiest
>and best way to represent a many-to-many relationship for data entry. I
>admit, I was a little stumped and replied that the form/subform was probably
>still the standard solution but he's got me wondering -- how does everyone
>else handle it? I can't see any reason to really defer from the
>form/subform, but now I'm curious what creative solutions others might use.
>
>The other question I have -- and this one's my own -- I know there are a few
>easy ways to handle new primary key values when entering the many side of
>the relationship first -- pop up forms probably being the most common and
>even combo box controls that allow new values -- what do you guys do? Do any
>of you force the users to enter the one side first, which often seems a bit
>unnatural to the data entry operator?
>
>These aren't really technical questions, so much as they are just
>design/solution type questions. I'm interested in hearing what other people
>do.
>
>Susan H.