Best Practice For Names Of People

Question

I am creating an Employee table in SQL Server. Existing databases at this company tend to put a person's entire name in one field. To me this seems to be limiting and error prone and I am inclined to use first name, middle name, last name. Is this the best way to handle names, or is having the entire name on one field sometimes preferred, and what are the reasons for this?

Answer 1

If you need to be able to sort or filter e.g. by last name, then it would be better to have separate columns for these fields.

If you're looking for reasons why a single "name" field might be preferred, here's a blog I ran across awhile back about Falsehoods Programmers Believe About Names that might be enlightening. If your user base is culturally diverse, some of your assumptions about what makes a name may be wrong.

Some of the systems that I work with on a daily basis tend to have two fields. First, a "login name" or "username" (or email address, in the case of Bugzilla) that is permanent and used to uniquely identify a user for login. Second, a "display name" or "real name" (in the case of MediaWiki) that can be changed by the user.

The systems I work with that have "first name", "middle name", and "last name" seem to be systems that are geared toward storing contact information (CRM systems or billing systems). My guess is that it's a holdover from paper forms (Rolodex, mail-in forms, invoices, etc.), but I have no references to substantiate that.