Thinking about his very briefly, I suspect you need two tables and a flag. The first table would be something like
person: ======= person_id <other unique information, such as pointer to image, date of birth, etc.> names: ====== person_id is_primary_name (Boolean) name For each person, you'd have one or more names with the one you want as the "working" name flagged. To retrieve a person, you'd search on names.name and might present the user with a list of choices, primary name first, sorted by person_id to group the alternative names together. Another presentation would be to group by person_id and use GROUP_CONCAT to present all of the person's names as one field. That might be easier for the user, rather than presenting a list to choose from. If a search turns up more than one person (as a search for "Pamela" might), then grouping by person_id gives the user the option of choosing the person they are looking for. When presenting the image, filmography, or such, you'd use the primary name. To change the primary name, your management interface would present each name separately with a checkbox or some such. Conceptually this seems reasonable to me, but I'm interested to see what others have come up with. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com > -----Original Message----- > From: Ian M. Evans [mailto:[EMAIL PROTECTED] > Sent: Friday, October 19, 2007 9:04 PM > To: mysql@lists.mysql.com > Subject: Need ideas on handling aliases and a.k.a.s > > I'm trying to wrap my head around dealing with people in a table that > have multiple names or akas. > > I run an entertainment news site and have to deal with people like > Pamela Anderson (who was once Pamela Lee, Pamela Anderson Lee, Pamela > Denise Anderson), Eva Longoria (who's now Eva Longoria Parker) and > Courteney Cox, who's Courteney Cox Arquette. > > I haven't really dealt with this yet, but I guess now I better handle > it > before I get stung too badly. > > Right now I have a people table that has: > > PeopleID > First > Middle > Last > Display > URL > > So as an example you'd have: > > PeopleID: 1078 > First: Eva > Middle: > Last: Longoria > Display: Eva Longoria > URL: evalongoria > > It's worked well for me. I have a peopleinphotos table...add Eva to a > photo caption and it's just a matter of grabbing her id number (1078) > and putting it in the table with the photoid #. > > She gets nominated, the input form looks up her id# and adds it to the > nomination table. > > I've been lucky in that most entertainers keep their public and > personal > names separate. But suddenly Eva wants her credits to read Eva Longoria > Parker. Sure I can add Parker to the Last field and remember to always > use Longoria Parker when I input new info, but what happens if she gets > divorced? > > Just wondering how some of you have handled akas/aliases/divorces for > things like customer databases. How do you ensure that a name change > doesn't actually cause a brand new record for the person if the data > entry person uses the old name, etc. > > Thanks for any advice. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
