CODEWITHSUNDEEP
sql-serverdatabase
MrBill
MrBill

Reputation: 100

Best Practice For Names Of People

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?

Upvotes: 5

Views: 3346

Answers (6)

Sharad
Sharad

Reputation: 813

I mostly use Prefix, First Name (contains First Name and Middle Name) and Last Name (contains Last Name and Suffix). It is useful for searching instead of storing Full Name in a single column.

Upvotes: 1

Eric Hauenstein
Eric Hauenstein

Reputation: 2565

Naturally this will depend a bit on the expected scope of your database (i.e. global, national, internal, etc.)

The most flexible option that I've seen some medical/legal systems use is a setup similar to this.

First, Middle, Last, Prefix, Suffix, LegalName, FullName, Alias

That may be overkill in this case, so perhaps this would work for you.

First, Middle, Last, LegalName, Alias

LegalName should be a fairly wide VARCHAR, to handle people with 3+ names, and the Alias column allows you to gracefully deal with marriage/divorce (from a data standpoint, not an HR one).

Upvotes: 1

pmcoltrane
pmcoltrane

Reputation: 3112

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.

Upvotes: 3

user3401335
user3401335

Reputation: 2405

i suggest to use first name and last name on two columns. if you have a middle name, it will saved in first name.

if you want the first name and the last name is possible with sql or c# or other language concatenate. If you save on one column, the order is fixed as you saved.(if you saved last name + first name, you can't show frist name + last name without using split, that is a bad solution)

is more accurate and clear.

Upvotes: 1

Rahul
Rahul

Reputation: 77866

Well, in most of my projects wherever there was a need to store customer information, names of customer are stored as same format. that way if full name is needed we can club 3 parts and get them.

Otherwise, storing entire name, means we have store it in proper format (I mean proper spacing etc); as well not every time we are required to fetch the full name. many people don't have a middle name (some even don't have last name)

first name, middle name, last name

Upvotes: 0

Cesar
Cesar

Reputation: 3519

Depends if you need the name separated, for example, in case of a filter by last name. This is very restricted by the use that you'll take for this field. At the moment I never needed this situation, I'm always used only one field for the person name.

Upvotes: 1

Related Questions