Should I use a Column naming convention or rely on column descriptions?

Question

Frankly I am stunned that after some basic (very basic) internet searches I have not been able to find a comprehensive naming convention or guideline or SQL object and column naming.

The parallel I'm used to is in the .NET development world where there are many offered conventions and Microsoft has gone so far as to document a comprehensive set of recommendations which is widely adopted:

• General Naming Conventions
• Design Guidelines for Class Library Development
• Guidelines for Names

1. Is there a well-adopted standard for database object, column, etc naming?

2. Are there any pitfalls with relying on the Description property for describing columns of a table? Obviously one wants the name to reveal intent but that's sometimes difficult in a database and descriptions can be invaluable for someone learning about the DB (although they can also lie).

Help much appreciated!

Answer 1

1. Pick a standard, make sure it makes sense, and document it. There have been plenty of debates about this (e.g. whether the identity column in the contacts table should be called ID or ContactID, or whether the contacts table should be called dbo.Contacts or the unnecessarily verbose dbo.tblContacts), and you're never going to find agreement among your peers. Which standard you choose is not important; what is important is that you use it consistently, make it well-known, and get agreement from your colleagues that this is the way.

2. Your columns should be self-documenting (and not cryptic, abbreviated or obfuscated) where practical. I would NOT rely on the extended properties, but that's just me (google/bing for problems with extended properties and you will see plenty of complaints I would guess). External documentation / data dictionary is much more valuable to me - if your database goes south and so does your backup, it makes it that much easier to rebuild. It also makes it easier to describe your schema to junior developers etc. without giving them direct access to everything.

Answer 2

Is there a well-adopted standard for database object, column, etc naming?

ISO/IEC 11179 Data Element Naming guidelines. I'm not sure what you mean by "well-adopted" but it is certainly comprehensive without being overly prescriptive. ISO means it is widely adopted.

Answer 3

My suggestion: Name things based on what they do or represent, and not how they do those things. In other words, don't name a table "tblCustomers", or else you may end up with a backward compatibility view named "tblCustomers", or similar stupidity. Likewise, don't do that with column names, so that you don't end up getting decimal values from "fltBalance".

The one exception is that I will occasionally prefix a view name with "vw" to make it clear that it's a view, rather than a table, and thus there may be performance implications of fetching data from it.

Answer 4

Personally I use the following convention for tables and views: Prefix the table name with the type of data it holds, which is usually pretty clear, but no t always. Here are some examples of the prefixes I use:

data_ (Tables that hold core data) ref_ (Reference tables that hold look-up values) xref_ (Many-to-many join tables) info_ (Meta data tables (may not apply in some situations, but in my case, stuff like tables that hold data about which versions of associated data or applications the database goes with, etc.)) app_ (Tables that are appended to other tables via views (Again may not apply to a lot of people, but in my case these are static tables that hold stuff like geographic information, which is appended to records by stuff like zip code, etc.)

and of course, the widely used fact_ and dim_ prefixes for facts and dimensions in data warehouses.

One could argue that such prefixes should now be implemented as schemas, but I like to reserve the schema for "who", as they provide a different level of security management.

Where I currently work we use all lower case for column names, and Mixed case, with underscore separators for table names, so like: data_Claim_Transactions.

We use all lower case for the column names in part due to the fact that we have a mix of Java and Linux, which are case sensitive. Also, since Oracle is case sensitive, I think even if you are using SQL Server, which isn't by default, its a good practice to design for case sensitivity. We use quite long column names actually, we have some around 100 characters long, but they are very descriptive, and given the nature of what we do, its kind of required. I'd say for an application database, you can get away with shorter column names. In data warehousing, you tend to need longer names, especially when you have lots of columns with similar content or if you have highly denormalized tables.