How do you deal with the "Many Names for 1 Person" problem?

Question

One of the most common problems I run into when creating any information system is the fact that people go by so many different names. Someone named "Bill Smith" may go by "Will Smith","William Smith", "Smith, Will", etc... If say, I wanted to write an application to link blog posts to authors, I would have to account for all those names.

My question is: What approaches do you take to keep consistent data throughout your application. How do you structure your database so that you can refer to a single identifier to locate all those names? What UI approaches do you take make sure that people enter in names in a consistent manner?

Answer 1

It's probably a good idea to accept only one name from your user, and allow them a "nickname" or a "public name". That gives them the freedom to have a legal name, perhaps for mailing or billing, and a public-viewable name for interaction on your site.

Beyond that, I don't think I would allow my users to have multiple names, unless my system required it. If I did, I'd split it up into two tables:

• Users:

  • userid (ex: 1821)

• UserNames:

  • userid (ex: 1821)
  • firstName (ex: Jonathan)
  • lastName (ex: Sampson)

In addition, you could add a field in the usernames table called 'isPrimary'. This would be a boolean value that will tell you which name to treat as the primary name for the user. This is similar to how wikipedia store a history of data/changes. They keep all, but mark which is "active", or in your case "primary".

Answer 2

As long as you have a unique id for each user (which is not their name) you can have a table that maps name variations to a unique id, and then associate each post with that unique ID.

(Table mapping names to UIDs)

Name        UID

Robert S    123456
Bob S       123456
Bert S      123456
Darren      987654

(Table with post information, including author's UID)

Title     Author  ...

Post 1    123456
Post 2    123456
Post 3    987654

(Table with author information)

UID     Preferred Name   Webpage                ...

123456  Robert Smith     http://www.robert.com
987654  Darren Jones     http://www.jones.com

Answer 3

It sounds to me like you are trying to use their name as a primary key or UID. This is the wrong way to go. You should have a seperate UID as the primary key, then the name can be whatever you want, and you can even have a list of alternate names.

Answer 4

I agree with the first 3 posts on how to structure your schema.

In regards to the UI I would allow a field for the persons legal first,middle and lastname which should change very rarely.

Then allow nickname(s) depending on your application requirements.

Having their full legal name can come in handy for billing/financial/HR situations too.

Answer 5

The real problem happens when you have multiple applications, and each one has their own schema for user information. The billing system might have "Will Smith"; the payroll system might have "William Smith"; the claims system might have "Willie X. Smith". All are really the same person. What do you do? It's a huge issue for stovepipe, legacy apps.

Answer 6

You could always make a AKA table, where you could have the prefer name to AKA name. So if someone uses the name Bill, you can always replace it with William.

I have never personally used this concept for names, but I do support a project that does something similar with Movie Titles, which can varied for different countries.