Standard Way of Storing Names in a Database

Question

I have recently been pondering names and the way we store them. Generally a person will have a First, Last and Middle name. If you want to be particularly complete you might add a suffix field, perhaps even a title field. So if someone wants to be "Dr. John Q. Public III", they can. But a person can have more than one honorific and more than one suffix. For that matter then could hve a hyphenate last name too. So what if you are "Dr. John Quintus Maximus Public-Doe III Ph.D. MD. RPh."? You could do:

Persons
    PersonID
    Prefix
    FirstName
    MiddleName
    LastName
    Suffix
PersonHonorifics
    PHID
    PersonID
    Honorific
PersonNames
    PANID
    PersonID
    NameOrder

But then it gets to be a bear to work with, and no one ends up using them anyway.

Is there a generally accepted "Standard Way" to store name data?

Answer 1

Sometimes you only think you know your requirements. The book publishing industry has an information standard called ONIX that uses the following. It's interesting to note that First and Middle names are combined into one field.

• Titles before names (ex: Professor, HRH Prince, Saint)
• Names before key names (first names and/or middle initials - ex: Brendan J. E.)
• Prefix to key names (ex: van, as in Ludwig van Beethoven)
• Key name (last name)
• Names after key names (ex: Ibrahim as in Anwar Ibrahim)
• Suffix to key names (ex: Jr, III)
• Qualifications and honors after key names (ex: MB, PhD)
• Titles after names (ex: Duke of Edinburgh)

Answer 2

I prefer AD naming style

First Name  givenName
Last Name   sn
Initials    initials
Display Name    displayName
Description description
Office  physicalDeliveryOfficeName
Telephone Number    telephoneNumber
Telephone: Other    otherTelephone
E-Mail  mail
Web Page    wWWHomePage
Web Page: Other url

Answer 3

The standard way is to look at your requirements, and store the data required. While this is an interesting academic problem, the truth is, in the dozens of systems I've worked on, first and last name is usually sufficient. Sometimes we will store a middle initial, but most of the time even that isn't required.

If you have a requirement to store all of Dr. John Quintus Maximus Public-Doe III Ph.D. MD. RPh's information, then you devise storage for that. But as long as your last name allows for enough data, then Dr. Maximus can type as much or as little as he would like to be stored about his name and titles.

Answer 4

Best to use a single full name field.

See: https://www.w3.org/International/questions/qa-personal-names

Names are culturally determined, so don't be blinded by your own culture.

Answer 5

I would start by looking at the vCard standard; it needs a bit of normalization.

Answer 6

You should design your storage format around how you expect to use the data. If you need to know the difference between the first name(s) and last name(s), then have columns for each. Likewise, if you (or your business) cares about suffix/prefix/middle names/etc... enough to want to use them in a specific manner (e.g. spamming all customers who are Doctors), then have columns for each. But if all your need it for is to identify them in a report, or in a email salutation, then consider an easier approach of: First_names, Last_Names, and leave it at that.

Ask yourself what realistic benefit your organisation would get out of storing each component of a persons name separately. Look at government forms and see how much information about a persons name they feel the need to capture.

Answer 7

From my experience it is usually

• Title (as a FK, linking out to a table of titles)
• Forename
• Middle name
• Last name
• Previous last name (if needed, to catch change of name for married women)
• Suffix

Anything else tends to be overkill (unless needed in your specific application) and a pain to manage

Answer 8

The following is a bad idea (see the first comment):

KISS! Free yourself and just use "FullName" :-) (In case you need it, the last word in the string is the "Lastname")

You can always use: Dear "Fullname".

How you handle honorifics depends on your requirements and your audience. You could collect "salutation".

Answer 9

Unless you are dealing with a specialized group of peole like doctors where you might need to store all the suffixes in an way that makes it easy to search (we often search on professional suffix here), then you can store them either in the lastname field or in a separate suffix field (makes searching all the people named smith more reliable if sufffix is a separate field). but with a comma delimited list. Same for prefixes. I would suggest that firstname, lastname and middlename (Helps distinguish dups and without the filed it is less likely to be put in even if the user knows it) are generally necessary for being able to properly search and report on the data. I wopuld also suggest a calculated field that formats the fullname the way you want it displayed on emails, etc.