Separate field for Initial in name

Question

A question (mostly) directed at US developers: what is the common way for entering and storing the initial in a name?

Two fields:
LastName = "Doe"
FirstName = "John, T."

or

Three fields:
LastName = "Doe"
Initial = "T"
FirstName = "John"

Also, will users expect to see the initial when the name is displayed or printed?

Answer 1

I always store the field as a separate value. I will collect it, if only to help distinguish between individuals with the same name from an administrative point of view. I don't usually use it in the user's UI, however, as people rarely use it. I expect people who go by their middle name will enter it in the first name field instead.

Answer 2

You only need separate columns if your queries are going to process them separately. That's why last names are usually in separate columns so you can sort and search on them.

In fact, if you could guarantee all names were entered as "Doe, John T", you could get away with just one column for the whole name.

I can think of no situation in which you'd want to search on the middle initial alone, and the first/initial column will always be entered as "John T", never as "T, John" so I don't believe you gain anything (search-wise) by separating them.

However, one possibility springs to mind: personalized letters. You may want to send a letter to a person in the database starting with "Dear John". In that case, it would be advantageous to separate them.

Answer 3

Look at the discussion in this SO question for some good information.

Answer 4

I'd like to point out that not everyone has only one middle initial. In particular I get hugely bugged having to choose one of mine

Brian A S Agnew