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.

Ian M. Evans wrote:
> 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.
>

Only use the id (primary key); the name should be treated as if it's
arbitrary. I mean, what would you do in your setup if you had two or
more people with the same name? So you can't rely on just the name.

Obviously, remembering the IDs for all of these people is out of the
question, so you should create a select list of the names with the IDs
as the option values. IIUC, you're submitting a name and either getting
a form with that person's info, or an empty form to enter a new record.
If that's correct, you risk creating new records because of a
misspelling. This is why i always have a select list of countries, for
instance, because there are so many different ways that someone might
fsck up the spelling of a country. It sees like you have a similar
situation.

So, you select the name you want from the list and, when your form comes
up with Ms Longoria's (who the heck is that?) info, you can alter the
name fields but the main identifying item--the ID--is just a hidden field.

Then the only thing you have to worry about with these name changes is
locating them in the select list.

Of course, you should also be doing some kind of search on *new* names,
just in case the person is in there under a similar name (eg. 'Eva
Longoria' -> 'Eva Longoria Parker').

But maybe i didn't grok precisely what is you need to do.

As for AKAs, you can create an aka table that lists these with foreign
keys pointing back to your people IDs. Thinking about it for all of 30
seconds, i'd guess that the easiest thing to do would be to make the
name a single field for this table, then add that table to your query
using fuzzy search (with the field having a FULL TEXT index).

brian

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:ianevans@digitalhit.com]
> 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/mysql?unsub=jschwartz@the-
> infoshop.com