What is better practice when storing a users full name in the database? 1 or 2 columns?

Question

Is it considered better practice to store a first_name and a last_name in 2 separate columns?

or would storing both in 1 column be ok?

Answer 1

from: Quora Why should a database design split the name of a person in different columns? answer. [Link]

In a database each specific data point should be a separate field. Its a lot easier to concatenate fields together than split them apart. In point of fact person names should be 5 fields; Salutation (Mr, Ms, etc.) First, Middle, Last and Suffix (Jr Sr, etc.). Another example is addresses. the Street name and street number should be separate. You might want to group all records on the same street. This wouldn’t work if the street number was included

I actually would use Salutation, FamilyName, GivenName, Suffix. Combining all given names in GivenName

In case you don't know if a part of the name is given name or family name (Ellie May Jones), just leave the unknown part consistently in either field.

To compare two people to see if they match, compare in this order: Salutation GivenName FamilyName Suffix

Then it doesn't matter if a part of the name is in the wrong field, the complete name will be the same.

Answer 2

Given Name and Family Name with Title is the best practice, as it is been used internationally like on passports and national cards. Also it would be easy to manage without any overheads in database and programming language. As I have used :

<div class="field item form-group">
                    <label class="col-form-label col-md-3 col-sm-3  label-align">Name<span class="required">*</span></label>
                    <div class="col-md-1 col-sm-1">
                        <select class="form-control" id="title">
                            <option value="">select</option>
                            <option value="Mr" selected>Mr</option>
                            <option value="Ms">Ms</option>
                            <option value="Miss">Miss</option>
                            <option value="Mrs">Mrs</option>
                            <option value="Dr">Dr</option>

                        </select>
                    </div>
                    <div class="col-md-3 col-sm-3">
                        <input class="form-control" data-validate-length-range="6" id="givenName" placeholder="given name" required="required" />
                    </div>
                    <div class="col-md-2 col-sm-2">
                        <input class="form-control" data-validate-length-range="6" id="familyName" placeholder="family name" required="required" />
                    </div>

                </div>

Above code generates this view

Whereas in database shown like below image:

Answer 3

Only you know that, really. It depends if you're ever going to need to get those parts of the name back out separately.

Storing first name and last name in a single column isn't a reversible operation (in "Ellie May Jones", is "May" part of the last name or the first name?)

Also, what different cultures are you going to be dealing with? Not everyone in every culture even has two names -- see the Wikipedia article on Family names to understand the can of worms you may be opening up :)

Generally, I'm used to systems storing given and family name separately, and this gives you more potential to manipulate that data later, but then I deal primarily with a single, fairly small geographical region where people are used to being asked for a forename and surname. Also, the systems I run need to search for people based on their family name (easier if you can index a separate column) and send letters to people starting "Dear Miss Smith...")

You may also want to consider whether you need a "preferred" name -- my name is "Matthew", for example, but I much prefer being called "Matt". And there are plenty of people who prefer others to use one of their middle names when addressing them. Whether you need to capture a preferred name and a "real" name will depend on your requirements...

If I were you I'd start with two columns, assuming a fairly normal, English-speaking cultural bias. It's not a lot of code/storage overhead, and you can easily convert to a single column later if you find some pressing reason.

Answer 4

Sure, just be sure you can deal with the german minister of defense, last thing you want is to cause a scene with the military because your code can't deal with his full name...

Karl Theodor Maria Nikolaus Johann Jacob Philipp Franz Joseph Sylvester Freiherr von und zu Guttenberg, and guess what? His "last name" is von und zu Guttenberg. He's not a one-off case either. De Luca is a family name, separated by a space.

I have a grandmother, and in Hungarian custom, a last name is actually your given name. Sztaki Helen is her proper full name, except her brother might be called Sztaki Fred, her father named Sztaki Greg. A similar thing exists for my Chinese friend Dai Wai, where Dai is also his family name.

As it turns out, names are damn confusing. Instead of researching and planning for all of these various rules just to figure out what someone's called, especially when you base it off of "first name" and "last name" (Don't call my grandmother Mrs. Helen, that's her given name not family name), step back and think about why you actually need to collect all this personal information about me.

I'd go on and totally blow your mind by explaining that not all cultures sort names alphabetically either, or that they even don't have a roman alphabet, but lets not go there just yet.

Do you actually need my personal information? Probably not. In some cases you will, in most... you don't. Don't ask for it.

If you really really do need it, ask for Sur Name and Given Name(s). Store it as two columns.

Answer 5

two or three should be good.

I prefer as two. it will be easy to search based on surname or first name

any way u can use 1 column as well.

Answer 6

The memory overhead is not worth mentioning in most cases: if you store it in one column, you'll have to store an additional char (whitespace) and if you use two columns you have the small stuctural overhead.

So I'd store the name in separate columns so there is no information lost - just in case you need it at some point in the future.

Answer 7

As you have a first_name and a last_name, then logic dictates 2 different columns are needed - else, don't collect them as two separate data items.

Also, you never know when you'll be asked to provide a list of first names only, for example.

The only time I'd say one column is the right solution is when you're collecting one field; name.

Answer 8

Depends on what you want to do with it but personally I'd use seperate columns and possibly an additional one for title, eg Mr, Mrs etc. Then you can easily pull out first name, surname, full name, full name with title, surname with title etc to suit your requirement.

Answer 9

Personally I would store them separately as you can handle the data a lot easier. Also, what if the person has a space in their name or accidentally puts it and you want just a first or second name, how ill you determine first or second names if that happens?

EDIT:

Also, if you want to store more detailed names, I would make a whole range of columns for Prefix, First, Middle, Last and anything you can think of.

Answer 10

Two separate columns is almost always better.

You can always compute the full name from two separate columns. It's much harder to split the name (given two word last names, middle names, suffixes, etc) from a single column.