Jul 272010
 

Do you like it when your schemata are easy to understand and maintain?  If so, keep the following in mind when choosing names for tables and columns.  If you’re feeling evil and want to inflict some frustration on others, this might give you some good ideas too…

1.  Data types make horrible names

There’s no rule that says you can’t name a column after a datatype – it will just be awfully confusing.  The following code works perfectly:

The birth of the DATE datatype in SQL 2008 definitely throws a monkey wrench in the works as well.  How many columns do you know of that are named “Date”? (More on that in a bit.)  Again, it’s not going to break anything, I just find it rather confusing when a column of type “datetime” is named “timestamp”.

2.  So do reserved keywords

Interestingly, SQL Server datatypes are not found on the list of T-SQL Reserved Keywords.  The rules for T-SQL identifiers state that a regular identifier cannot be a T-SQL reserved keyword.  “Regular identifiers” are those which do not require brackets or double quotes around them.

3.  Pick a good name length

Bad NameA good name should be long enough to be descriptive, but short enough that it’s not a pain to type.  I hate columns or tables with names like “Date” or “Name”.  Chances for confusion can easily be lowered by adding another word to make it more descriptive, such as “PurchaseDate” or “FamilyName”.

Table and column information is stored in the sys.tables and sys.columns tables.  The name values are stored in columns of the data type sysname, which since SQL Server 7 has been equivalent to nvarchar(128). This is one of the cases where adding quotes or brackets can’t help you break the rules. Names cannot exceed 128 characters in any case, and temporary tables are a special case as they can’t exceed 116.

4.  Avoid spaces and special characters

They’re allowed, but I consider it a bad practice to use them.  Most special characters are not included in the rules for regular identifiers, meaning that you’ll need to enclose the name in double quotes or brackets.

5.  They can be case-sensitive depending on collation

Case sensitivity in object names depends on the database’s collation settings.  If it is case-sensitive, then object names will be unique based on case sensitivity as well.

6.  Don’t pick names that will change meaning

The concept of a name that changes meaning might not make a whole lot of sense, so I’ll elaborate with a short story.  In a previous job, we had to maintain a table that stored historical information for the previous 10 years.  Said table had 11 columns:  Key, Year0, Year1, Year2,…,Year9.

Year0 was always the current year, Year1 was last year, etc, so each year the columns changed meaning as far as what calendar year they really referred to.  There was also a special job that had to be run once a year to shift all the data one column to the right.  This is more than just bad naming, it’s a horrible design to begin with!  We knew there was a much better way, but were stuck with this schema due to legacy application support.

In conclusion, a little thought when choosing table and column names can go a long way. I hope this is helpful!

  2 Responses to “Six Tips For Avoiding Bad Table and Column Names”

  1. Oh, don’t leave us hanging, exactly what IS the better way that we knew about to design the table whose columns were (key, year1-9)? :) If only people weren’t so used to looking at that data horizontally in a spreadsheet-style interface…

    • Actually the table you’re thinking of isn’t what I was referring to, but I would have done it in a 4 column table: {DataDate, DatapointName, Key, Value}, that way nothing would have to change or shift. As for the spreadsheet-style interface, I would have either accomplished that with a pivot or left it up to the application developers to handle :)

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

(required)

(required)