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:

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!

  • 1
    #2 sufficiently answered, b/c description is an extended property it is easily lost -- I know this from experience since scripting frequently disregards extended properties.
    – kingdango
    Commented Feb 17, 2012 at 14:24
  • Yes, extended properties are ignored by a lot of 3rd-party scripts and tools, as well as many of the native options. Commented Feb 17, 2012 at 14:27

4 Answers 4

  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.

  • I appreciate the reminder about standards and the point is well taken; however, I've found it's best to not re-invent a standard (make one) but to "pick one" that is at least well known if not well adopted. So my follow-up to you is... is there a well known database object naming convention (or two)?
    – kingdango
    Commented Feb 17, 2012 at 13:40
  • +1 - Storing a description of the data with the data can lead to issues since you will lose both if you lose one
    – JNK
    Commented Feb 17, 2012 at 13:41
  • 1
    I like very much PerformanceDBA's answer here: Relational table naming convention Commented Feb 17, 2012 at 13:43
  • 1
    @ypercube yes, it's a thorough answer, but I already don't agree with some of the specifics (e.g. user_product - I don't like all lower-case, I don't like underscore for conjunction of entities, and I prefer plural since a table contains a set). Commented Feb 17, 2012 at 13:44
  • 1
    There is a standard that I think it includes naming conventions: ISO/IEC 11179. No idea how spread its use is. Commented Feb 17, 2012 at 15:31

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.


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.

  • 1
    In those cases I would use view_ - what does vw do other than save two keystrokes? I don't understand the desire to shorten these meaningful words into much less readable muck. YMMV. Commented Feb 17, 2012 at 14:22
  • Fair enough, but I can't think of a whole lot of other interpretations of "vw" as a prefix, unless you work for Volkswagen or something.
    – db2
    Commented Feb 17, 2012 at 15:34
  • It's not about interpretation, it's about trying to read words without vowels. Or when you say "deploy viewSuchAndSuch" and the anal retentive gatekeeper comes back and says he can't find an object with that explicit name. Commented Feb 17, 2012 at 15:47
  • I don't run into those issues around here, so for a frequent, standard prefix like this, I don't care if it's "v", "vw", or "view", so long as it's consistent. Table/object names, I prefer verbose, unambiguous names. What does "Con" mean? Contract? Consignment? Contact?
    – db2
    Commented Feb 17, 2012 at 17:10

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.

  • I'll add one more thing to this, which is that in terms of column naming, I like to use a convention of placing the noun first, then the adjective, which isn't how we talk, and consequently not how many people name columns, but this makes columns easier to organize. For example, instead of using Home_Phone, Mobile_Phone, Work_Phone, you would use Phone_Home, Phone_Mobile, Phone_Work, etc. Commented Feb 17, 2012 at 20:18
  • You can use schemas now for Data and Ref prefixes: no need to include it in the table name.
    – gbn
    Commented Feb 20, 2012 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.