[AccessD] Re: Normalizing issue

In a normalized world, they are two parties with individual names that are 
related to each other by a specific type of relationship.  Translated to db 
design:

tblParty
PartyID
First_Name
Middle_Name
Last_Name
Name_Suffix

tblPartyAlias
PartyAliasID
PartyID
GoesByName

tblPartyRelationship
PartyRelationshipID
PartyID
RelatedPartyID
RelationshipType
RelationshipGreeting

In the case you listed below, the field RelationshipGreeting would hold the 
combination of their names the way they wanted them.  You could have it 
default to "Joe and Sue Smith" after setting a relationship type of 
spouse.  The edit it as needed for the special cases.  This model also 
incorporates an alias table to hold the solution to what Susan needs for 
her pen names.  All she would need to do is add a table to relate the pen 
names to the publisher for the articles being published.

Arguments aside, if you think the design through, you will not need to 
argue. :-)

Robert


At 12:00 PM 6/23/2005, you wrote:
>Date: Thu, 23 Jun 2005 11:54:53 -0500
>From: "John Bartow" <john at winhaven.net>
>Subject: RE: [AccessD] Normalizing issue
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID: <200506231654.j5NGsrX0119546 at pimout1-ext.prodigy.net>
>Content-Type: text/plain;       charset="us-ascii"
>
>I think so. But I hope you all come with a good answer to this so I can
>store it in my tips & tricks db :o)
>
>I've run into one really bad name example some years ago. I had an app that
>tracked donors for an organization. The couple wanted BOTH of their names on
>the address label and she used a hyphenated name but he didn't. Something
>like "Joe Smith & Sue Jones-Smith".
>
>I could have easily tracked them as individuals but they only wanted ONE
>piece of mail.
>
>I also could have also tracked them as an Organization or Business and used
>"Joe Smith & Sue Jones-Smith" as an Organization name, with both of them
>listed as contacts for the Organization but that didn't cover every single
>instance of mailing and/or reports quite correctly and my customer didn't
>want to offend any of their donors by accident.
>
>I think we ended up putting his first & last name in the firstname field and
>her first name and hyphenated last-name in the lastname field. Which of
>course messed up the alphabetical internal lists of donors.
>
>I considered adding a spousename field and doing some complicated queries to
>get things correctly listed on the label but thankfully (for me anyway) they
>left the area and quit donating to the organization - problem solved! So the
>customer was happy and I quit thinking about it.
>
>John B.
>
>
>This is one of those never ending arguments, isn't it.  :)
>
>Reuben Cummings