Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email email@example.com
Date: Fri Oct 19 2007 - 21:10:01 CDT
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:
> So as an example you'd have:
> PeopleID: 1078
> First: Eva
> 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
> 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
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).
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql