First name, middle name, last name. Why not Full Name?

Question

I am trying to find a better approach for storing people's name in the table. What is the benefits of 3 field over 1 field for storing persons name?

thanks.

Answer 1

You may want to search on the 3 separate fields for one and its inexpensive to concatenate for the fullname.

e.g. If you want to search for all the Mr. Nolans your query would be

SELECT Title+' '+FirstName+' '+Surname As FullName  
from table where firstname = 'Mr' and surname ='Nolan'

to do this with just the fullnames would be a pain.

Answer 2

You can always construct a full name from its components, but you can't always deconstruct a full name into its components.

Say you want to write an email starting with "Dear Richie" - you can do that trivially if you have a given_name field, but figuring out what someone's given name is from their full name isn't trivial.

You can also trivially search or sort by given_name, or family_name, or whatever.

(Note I'm using given_name, family_name, etc. rather than first_name, last_name, because different cultures put their names in different orders.)

Solving this problem in the general case is hard - here's an article that gives a flavour of how hard it is: Representing People's Names in Dublin Core.

Answer 3

Each of the individual names is an atomic piece of data. When they are stored separately then it is easier to print them out in different formats such as Firstname Lastname and Lastname, Firstname.

Answer 4

About the only thing I can think of is for searching purposes. It's a bit better to search a field using [=] rather than say [like].

If you have no need to display the name as seperate words then go with a single field.

But if you need to do something like [Dear Mr. Achu] then perhaps a 3 field approach would be better.

Answer 5

There is no benefit if you never need to sort or search by first, middle, or last name.

Answer 6

Flexibility.

e.g. If someone had a double barreled last name and no middle name.

Answer 7

Keeping the fields separate allows you to support different output formats and cultures where the family name is written first

Answer 8

Most of the time it's there to support writing form letters like, "Mr. so-and-so", or to search/sort by last name which is very common.

Given that first/middle/last may not apply to all cultures, there could be a better approach. It might be better expressed as "informal name" / "formal name" / "legal name" or something like that.

Still, at this point first/middle/last is very common, and from a data entry standpoint it is what everyone expect.

Answer 9

To me it is simply better to store 3 names so that explicit parsing is necessary later on if the individual components are needed..

Answer 10

As others have said, how do you decompose a full name in to its component parts.

• Colin Angus Mackay
• Jean Michel Jarre
• Vincent van Gogh
• Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Cipriano de la Santísima Trinidad Ruiz y Picasso

How do you reliably decompose that lot?

Answer 11

I was looking up the Spanish Civil War the other day, and found this exception to most rules:

• Francisco Paulino Hermenegildo Teódulo Franco y Bahamonde, Salgado y Pardo de Andrade
• Father: Nicolás Franco y Salgado-Araújo
• Mother: María del Pilar Bahamonde y Pardo de Andrade

---

Next time I'm working on a system that has to store names, I'm going to try something radical: designing from the requirements.

What are we going to use the names for?

1. Name on an address label for the postal service
2. Greeting on the website
3. Informal name

Based on what the names will be used for, we'd determine how much information to store. Maybe we allow the user to enter all three of those, including line breaks in the first case (Generalissimo Franco might want his full titles and appointments listed, if he weren't still dead). Maybe we provide First, Middle, Last, Generation as an option, and fill in the rest as defaults. Maybe we offer other common options like Surname, Given Name.

This is in contrast to the old-style First, Middle, Last we've used since before I started programming in COBOL back in 1975, and have "made fit" ever since.

Answer 12

You can't always separate surname from full name cleanly and reliably so there's good reason to separate that because you often need surname. After you do that, there are two common approaches:

1. first_name and middle_name; or
2. given_names.

(2) is arguably more preferable because people sometimes have more than tow given names and (1) is more inflexible in this regard.

Also, another common field is preferred_name (in addition to the above).

Answer 13

Keep your data as clean as you can!

How?

Ask your user only as few things as you absolutely need at the time you ask.

How you store the name does not matter. What does matter is that

1. the user experience is as good as can be
2. you don't have false data in your system

If you annoy the users with mandatory fields to fill in and re-question them several times, they can get upset and not buy into your application right there and then. You want to avoid bad user experiences at all times.

No user cares how easy it is for you to search your database for his middle name. He wants to have a easy, feel good experience, that's it.

What do users do if they are forced to input data like their postal address, or even email address when they only want a "read-only" account with no notifications needed? They put garbage data into your system. This will render your super search and sort algorithms useless anyway.

Thus, my advice would be in any app to gather just as little information from your user as you really need in order to serve them, no more.

If for example you run a online shop for pet food, don't ask your users at sign-up what kind of pets they own. Make it an option for them to fill in once they are logged in and all happy (new customers). Don't ask them their postal address until they order stuff that is actually carried to their house, stuff they pay for and thus care that YOU have their exact coordinates.

This will lead to a lot better data quality and this is what you should care about, not technical details the user has no benefit from....

In your example I would just ask for the full name (not sure though) and once the user willingly subscribes to your newsletter, let the user decide how he/she wants to be addressed...

Answer 14

I voted up some of these answers, but if you are looking to avoid repetitive or redundant or messy concatenation in your code, you can always use a computed column in the database or a method in a class which exposes the name consistently reconstructed. If these concatenations are expensive (because you are printing a million statements), you can use a persisted column.

Often you will allow users to specify names like nicknames or friendly names, so that you aren't referring to them by the name in their records or always as Mr. Smith.

It all depends on your requirements. There is no single good answer without the environment it is expected to satisfy.

Answer 15

Unfortunately this is kind of like asking what is the best way to store a number in the database. It depends on what you are going to do with it - sometimes you want an int,other times a byte, and sometimes a float. With names it depends on things like what cultures do you expect your users to come from, what you plan on doing with the names (will you be using these names to connect with another system that stores names as "last name, first name"?), and how much you can afford to annoy your users. If this is an internal HR application, you can probably afford to annoy the users a lot, and have a very structured, formal breakdown of name components (there are way more than 3 - don't forget mr/mrs, jr, III, multiple middle names, hyphenated last names, and who knows what else if you are trying to handle names from all cultures. If you have a webapp that users might or might not care about, you can't ask them to care too much.

Answer 16

Things like ORDER BY firstname or ORDER BY lastname are possible when you break the name up into multiple fields.

Not as easy to do when you mash all names into one field.

Answer 17

The i18n issue can be a bugger either way. certain cultures use the surname first and the given name last, that strikes the idea of first and last names so we move to fields for surnames and given names. Wait, some cultures don't have a surname or the surname is modified by the gender of the named.
We can get into tribal cultures where the person is renamed on adulthood. "Sitting Bull" childhood name was "Jumping Badger".
This is somewhat of a ramble but what I am showing is that the more fields you have the more accurate the design is. There should be at least a not null 'given name' field and a optional 'surname' field tied to a PK that is an integer. If the aforementioned requirements are observed, fields can be added without issues of breaking queries.

Answer 18

Not sure how practical it would be, but maybe if cultural sensitivity is important in the context of the application being developed, perhaps a name should be a collection with each element of the collection carrying a value indicating if the name is the addressable "first name" or the addressable "surname" and so on for "title" or anything else that needs to be identified. A name ID could be used to identify the order of the elements for re-composing the full name.

Answer 19

Just have two fields, 'Full Name', and 'Preferred Name' - easy. Supports every name in existance (As long as the language has lexical symbols... So, yes, that excludes languages that do not have a written form).

Just make sure that they are handled in some unicode format, and that application code properly handles unicode conversion.

Answer 20

Some of the issues can be solved by also storing an additional column like PreferredName. We do that in our DB and also store prefix column and a suffix column. e.g 'Prof Henry W Jones Jnr' with preferred name as 'Indiana Jones'.

Answer 21

Here's the thing, not even humans can get this right all the time, there's just too much data, and too many special cases. I could change my name right now to be 20 parts, with the middle 13 as my "first" name. Parts of names can contain any number of words, and there can be any number of parts of names. Some people only have 1 name (no surname). Some people have lots of middle names. Some people have first or surnames composed of several words. Some people list their surname first. Some people go by their middle name. Some people go by nicknames that aren't obviously related to their given name.

If you try to guess these conventions in software YOU WILL FAIL. Period. Maybe you'll get it right some of the time, maybe even most of the time, but is even that worth it? In my opinion you should store names as one field and stop trying to be cute by using first names to refer to a person. If you need additional information about a name (e.g. a nickname), ask the user!