How should you record a person's name without resorting to "title, first name, surname" fields?

Question

For our application, which has a website and a backend, we really like the notion of capturing a "full name" for people to allow for the many different ways of expressing someone's name. However;

• The payment service provider wants "all first names, surname" which are obligatory for card payments
• The newsletter guys want a singular "first name" so the company can be seen to be more 'personal'
• The accounts people want to be able to sort by "surname"
• We programmers have noticed the following clients in our database:
  • Simon Mária – her “first name” is Mária and her “surname” is Simon
  • Danesh – only one name (although I assume it’s different written in the appropriate character set!)
  • 陳心蘭 – not sure which is the first name or surname (presumably, this client would have written in a roman character set if forced to do so)

These make it necessary to capture each of the elements of someone's name separately, and somehow making names that don't really fit into "title, first name, surname" pattern fit anyway.

So, without having a field for every part of "Right Hon. Chev. John Barrington Smythe Doe III OBE LLD MEng" or only one field where just "John" might be entered, how do you structure your application and database in practice so that you don't get caught out later?

I've already looked through the SO posts such as: First name, middle name, last name. Why not Full Name? and Person name structure in separate database table but haven't seen any examples of how people implement the best practice method (capturing "full_name") in the real world when having to deal with other systems which perhaps aren't current or best practice...

Thanks!

EDITED - for clarity

Answer 1

Firstly, we need to separate the problem between "presentation" and "storage". The 3 examples you give are all presentation problems, and could easily be dealt with by storing names in "title, firstname, lastname" fields.

The most unambiguous way of capturing the data is to have a form with explicit fields for each part of the name. Different countries have different conventions on how people enter names - much of continental Europe uses "LASTNAME, firstname" as the convention for filling forms, for instance. You can avoid confusion by having explicit form fields for each name part.

The most flexible way I've seen storage implemented is to use the concept of "name part", and to store name parts in child tables. You might want to add a namepart for "known as". For instance:

table People
Person_ID    d.o.b
1            1 Jan 1988
2            3 Feb 1989
3            11 Oct 2001

table NameParts
namepart_ID     description
1              Title
2              FirstName
3              Lastname
4              Suffix

table Person_Name
person_id    namepart_id      value
1            1                Right Hon.
1            1                Chev.
1            2                John 
1            2                Barrington
1            3                Smythe-Doe
1            4                III 
1            4                OBE 
1            4                LLD 
1            4                MEng
2            2                Joe
2            3                Blob
3            1                Ms
3            2                Mary
3            3                Hinge

Answer 2

The best response I have came from a similar question:

Personal names in a global application: What to store

The answer by Pontus linked to a document, which doesn't specifically describe how to record a name in a database, but it does provide the most complete description of the problem so far:

http://ns.hr-xml.org/2_5/HR-XML-2_5/CPO/PersonName.html

Perhaps if anyone has any experience of database implementations which adhere to this, then feel free to add another answer!

Answer 3

Well it looks like you will definitely have to have 'title', 'first_name', and 'surname' fields.

Then it might be best to have a 4th field like 'full_formal_name'.
It depends on what you want to use the names for, and who you are expecting to use your website.

It might be worth having several fields, title(s), first name, middle name, surname, qualifications(or whatever you want to call them, this could also be for generational names, ie III)

You'd need more fields the second way, but your users won't have to re-enter data for their full name.