CODEWITHSUNDEEP
phpmysqlperformance
Cosmin
Cosmin

Reputation: 1490

MySQL name and surname in 2 columns vs name in 1 column

One simple question and I couldn't find any answers to id :

Should name be in 2 different DB columns ( name / surname ) or in 1 column ( name + surname ) ?

In all the projects I had they were in 2 different columns, but now I have to start a new project and I was wantering how it better to store it. I mean, the 2 different columns gave me a bit of trouble and sometimes slowed performance down. Please note this very important thing :

So, what do you suggest ? 2 columns or 1 ? I am inclined twords the 1 column solution because I cannot find any advantages in using 2, but maybe I am wrong ?

EDIT

Thank you for the answers. The only reason for this question was for the performance issue, I need all the extra boost I can get.

Upvotes: 3

Views: 2051

Answers (5)

John Woo
John Woo

Reputation: 263893

In my opinion, I'd rather designed it as two different colmns because you can have various ways to handle the record. About performance issue, add an index on two columns to make faster searching.

There are times when you want to search for John Doe and wanting that even it is reverse Doe John but still matches to John Doe. That's one advantage of having separate fields on the name.

Sample design of schema,

CREATE TABLE PersonList
(
    ID INT AUTO_INCREMENT,
    FirstName VARCHAR(25),
    LastName VARCHAR(25),
    -- other fields here,
    CONSTRAINT tb_pk PRIMARY (ID),
    CONSTRAINT tb_uq UNIQUE (FirstName, LastName)
)

Upvotes: 0

podiluska
podiluska

Reputation: 51514

Two columns is much more flexible. Eg.

  • Do you ever want to sort by surname?
  • Do you ever want to address the person formally (eg: Dear Mr Cosmin)?
  • Will you ever want to search by surname and not forename, or vice versa?

200K records is a trivial amount in any properly designed database.

You may find this an interesting read on the subject of names

Upvotes: 3

Kamil
Kamil

Reputation: 13941

Using 2 columns helps you in:

  • easy sorting data by surname
  • communication with user by name (eg. "Hello Michael" used on many websites etc.)
  • displaying a lot of data in multiple columns (you can display only surname when you have no space on screen)

Names stored in format "Surname Name" is still easy to sort, but may be seen as inelegant in some countries.

Upvotes: 0

Charles R
Charles R

Reputation: 441

With two columns, you can sort by surname without having to do expensive substring operations in your select statement. It is easy to do a CONCAT to get the full name in situations that call for it, but harder to parse the last name out of names such as "John Doe-Smith" or "John Doe III".

Upvotes: 0

Marc B
Marc B

Reputation: 360872

The point of a relational database is to relate data. If you store a full name (e.g. John Smith) in a single field, you lose the ability to easily separate out the first and last names.

If you store them in separate fields, you can VERY easily rejoin them into a single full name, but it's quite difficult to reliably pull a name apart into separate first + last name components.

Upvotes: 5

Related Questions