Best naming for a column in a SQL Server table

Question

Which of the following three options would you choose for a column name in a SQL Server table, and why?

1. YearToDateWages
2. YTDWages
3. YtdWages

Follow up:

1. SSN
2. Ssn
3. SocialSecurityNumber

Answer 1

I'd suggest using more or less the same rules as naming classes. If the abbreviation is common knowledge (such as SSN or YTD) then abbreviate, but if it's not totally obvious what the abbreviation would stand for, then spell it out.

Whether you write it in uppercase or use PascalCase is a matter of personal preference.

Edit: Based on other answers, perhaps YTD isn't as well-known as I thought. If it's in a financial or trading app then you're probably safe with the abbreviated version; always be mindful of your audience.

Answer 2

It depends on how well-known the abbreviation is. My rule of thumb is what I'd say in a conversation. So I would write out "YearToDate", but keep "Ssn" abbreviated.

Answer 3

For the first one, I would use

WagesYTD

because YTD is a modifier on Wages.

For the second one,

SSN, or
SocialSecurityNumber

I never use camelCase in database field or table names, and Ssn is not a real word, nor is it a valid abbreviation.

Answer 4

Take a look at the ISO-11179 Naming Conventions specifically part 5

Part 5: Naming and Identification Principles, provides guidance for the identification of administered items. Identification is a broad term for designating, or identifying, a particular data item. Identification can be accomplished in various ways, depending upon the use of the identifier. Identification includes the assignment of numerical identifiers that have no inherent meanings to humans; icons (graphic symbols to which meaning has been assigned); and names with embedded meaning, usually for human understanding, that are associated with the data item’s definition and value domain.

Answer 5

If you're writing out the queries by hand, then shorter is better. After writing a few queries with SocialSecurityNumber repeated a number of times (e.g. in the select list, a few join clauses, a where clause and and the order by clause) then shorter will seem much better. Abbreviations are easiest to read with consistent case, e.g. SSN, and when the noun comes first, e.g. WagesYTD. This is particularly true if you have other fields relating to Wages, e.g. WagesOvertime.)

Answer 6

I think this depends on the maintenance scope of the application/database. If you're the only one who will ever see the database from a raw standpoint or the application code, you should probably name it what makes the most sense to you. I often name SQL fields with underscores separating the data type from its modifier, thus wages_ytd and user_ssn would suffice.

However, rarely can we predict the future, and you never know when your small app will wind up being worked on by others. In no case should you ever name variables cryptically or in a way that nobody else would figure out, for that reason. That, and I've come back to my old programs sometimes and thought, "What is that variable??" If that ever happens to you, your names aren't verbose enough.

The usual reason I abbreviate or use shorter variable names is if I get tired of repeatedly typing or referring to it (Intellisense helps but it's not always available), or if my code is scrawling across the entire widescreen monitor and I am having trouble because of long verbose variable names, like DiagnosticsWidgetFailureCount.SpecificWidgetProperty.FilterBySomeOption.