Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
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 Feb 17 '12 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. –  Aaron Bertrand Feb 17 '12 at 14:27

4 Answers 4

up vote 5 down vote accepted
  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.

share|improve this answer
    
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 Feb 17 '12 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 Feb 17 '12 at 13:41
    
No, my point was that if you go to any two sites where they talk about their standard, they'll be different. I don't know of one that's "the authority" because nobody will be able to define a standard that more than a handful of seasoned DBAs will agree to. The key is to take the suggestions you find and implement the parts that make sense to you. –  Aaron Bertrand Feb 17 '12 at 13:42
    
I like very much PerformanceDBA's answer here: Relational table naming convention –  ypercube Feb 17 '12 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). –  Aaron Bertrand Feb 17 '12 at 13:44

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.

share|improve this answer

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.

share|improve this answer
    
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. –  Aaron Bertrand Feb 17 '12 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 Feb 17 '12 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. –  Aaron Bertrand Feb 17 '12 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 Feb 17 '12 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.

share|improve this answer
    
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. –  RGPSoftware Feb 17 '12 at 20:18
    
You can use schemas now for Data and Ref prefixes: no need to include it in the table name. –  gbn Feb 20 '12 at 9:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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