'first_name' vs 'name_first' for Grouping Similar SQL Columns

Question

When creating a table with similar bits or data such as names. Is it preferred to have the column name prefixed with a common value?

Consider a table to store user information. Looking at the columns related to the users name they may be structured as follows:

title
first_name
middle_name
last_name

Would it not be better to style all columns relating to the users name with 'name' (as shown below) leading to more consistent naming patterns?

name_title
name_first
name_middle
name_last

• Brandin: I prefer to name entities as closely as possible to what domain experts call those things. For example, I think the relevant experts and forms that I've seen call it "middle name", not "name middle" (name middle could be interpreted as the middle of a name, which is something else). If you want to group for some other reason, use your own prefix e.g. "x_title", "x_first_name", "x_middle_name" and so on. x is your own prefix which has some convenient meaning to you as a programmer.

Answer 1

The standard is to use first_name, last_name, etc.

I mean standard not as formally defined (say in ISO 9000) but informally, a usage I have observed over 30 years in dozens of companies and hundreds of systems, thus an informal standard. There's nothing to stop you doing things a different way if it makes sense for your situation, however:

• Developers already expect names to use the last_name, first_name format in current web pages, scripts and database schemas. When they are that way the developer doesn't need to learn anything new and that makes development easier as they focus on more important code.
• Other systems mostly use it so interfaces are often easier
• First time readers don't have to pause to examine the code/data to understand the reason for the unusual naming
• Most SQL applications that I know, from command line to GUI tools, tend to display database table columns in the order they were listed in the create table (and adjusted by any modify table) commands. Thus the fields are frequently listed together nicely as you would like.

• Jacob Wood: Great points. Is there somewhere I can find a list of common naming standards?

Answer 2

I always used to to the name_first order as it effectively groups the fields making intellisense easy to use.

However, it can get annoying when you are refactoring. eg:

iteration 1:

table person :
 firstName
 address

iteration 2:

table person
 firstName //todo:: change this everywhere to match the new scheme
 nameSecond
 address

Additionaly if you have many of these it suggests you may need more tables

table person
 nameId
table name
 id
 first
 last
 middle

Although obviously with the name example this is a bit overkill.

..thinking about it though, people can have n names maybe

table name
 personId
 type //first, middle, last, nickname etc 
 value

is better!!