John Bartow
john at winhaven.net
Thu Jun 23 12:50:35 CDT 2005
Sounds good, I'm going to give this a whirl when I get a chance. Thanks, John B. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert L. Stewart Sent: Thursday, June 23, 2005 12:33 PM To: accessd at databaseadvisors.com Subject: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com