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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
