I’ve seen and worked with a lot of naming conventions. When I start a new development project, I always — without exception — document how I intend to name the new items I create, whether they are physical objects such as tables and fields in a database, or names used in code for objects, variables, and the like. This document is shared with the team, adjustments are made where necessary, and adopted as standard for the project or group of projects.

The important thing in this effort is consistency, and not the technique we adopt. If all single-part surrogate key fields are to end in “Key”, and all single-part varchar business key fields are to end in “ID”, then the rule must be obeyed by all developers and DBAs.

A major point of contention that seems to crop up every single time naming conventions are discussed is how and when to use an underscore. Should the field be “CustomerID” or “Customer_ID”? “ProductKey” or “Product_Key”? “SSNumber” or “SS_Number”?

CamelCase

boy and camel 150x150 Naming Conventions and the UnderscoreI have a general rule: If the container (database, property window, etc) can remember case, then only use an underscore when combining an acronym with additional information, when that information comes after the acronym. This is because you can use “CamelCase“, which I find more readable.

Here are a few examples, showing how I would nornally handle underscores when case is remembered:

  • CustomerID and not Customer_ID
  • ProductKey and not Product_Key
  • PhoneNumber and not Phone_Number
  • SS_Number and not SSNumber
  • ISO_Date and not ISODate
  • WP_Theme and not WPTheme
  • ValueEPS and not Value_EPS

And while I’m at it, avoid redundancy: VIN (Vehicle Identification Number) is not VIN_Number; GICS (Global Industry Classification Standard) is not GICS_Classification; and DG (Disease Group) is not DG_Group!

No Case Support?

If the container does not remember case, then always use an underscore to separate the parts of a name. Examples:

  • customer_id and not customerid
  • product_key and not productKey
  • phone_number and not phonenumber
  • ss_number and not ssnumber
  • iso_date and not isodate
  • wp_theme and not wptheme
  • value_eps and not valueeps

If you can be strict about naming, project members and future generations will have an easier time understanding your code, objects, and documentation. I find that the underscore — even within a good naming system — is often used inconsistently. I also find that the underscore can make a major visual impact if used correctly. So it pays to pay special attention to ASCII character 95!

As an aside, naming conventions also play an important role in data warehouse conformity. So while it is important to have good standards, you will also need good governance to be sure that your names are meaningful and consistent.