Practical Methods: Naming Conventions

Naming conventions are used to streamline development when multiple developers are working on a single system, as well as to simplify ongoing maintenance during the Development Lifecycle. There are many different naming conventions for SQL Server and no single convention is necessarily right or wrong. However, a single consistent naming convention should be followed within each database to reduce confusion and to enhance usability. Ideally, follow a single naming convention throughout all SQL Servers in an organization.

Databases

• Single database applications may use any simplified name. Multiple database applications should use a prefix followed by the database category.
• Samples:
  • "Finance" for Financial Operations
  • "Operations" for Operations
  • Prefix of "HR" for Human Resources
    • "HRData" for the primary database
    • "HRImport" for data import holding tables and procedures
    • "HRExport" for data export holding tables and procedures

Backup Files

• Prefix all backups with the database name. Use an underscore and add the date and time of the backup.
• Samples:
  • Full backup: dbname_200601011800.bak
  • Differential backup: dbname_200601011800.dif
  • Transaction Log: dbname_200601011800.trn

Users and Logins

• Match all database user names to the mapped login. This simplifies security audits.
• No user accounts should be shared among logins. Use database roles to achieve continuity instead.

Tables

• Prefix all tables with "t".
• Complete the name with the primary entity stored in the table in a singular form.
• Name tables that rely on other tables in sequence using the primary table name as a starting point.
• Name many-to-many tables with both primary tables listed alphabetically. Separate the names with an underscore for clarity.
• Holding tables for temporary data should be prefixed with "temp".
• Samples:
  • tCompany, tCustomer, tProduct, tInvoice
  • tCompanyAddress, tCustomerAddress, tInvoiceDetail
  • tRole_User, tPermission_Role
  • tempCustomerBackup
• Comments:
  • The prefix used here helps ensure that no keywords are used as a table name. For example, "user" is a common desired table name that is also a keyword, but "tUser" is not.
  • Never, ever link a stored procedure to a table with the "temp" prefix. It is a virtual guarantee that this will lead to a "temp" table being used in production. Sooner or later someone will delete this "temp" table and break your production system.

Columns

• Name columns according to the information they contain. Primary keys should use the table name plus the suffix "ID".
• Samples:
  • CustomerID, CustomerName, CustomerNumber, Address, City, Country
  • FirstName, LastName, Phone
  • CreateOn, CreateBy, EditOn, EditBy, DeleteOn, DeleteBy
• Comments:
  • Be consistent with column names between tables. Don’t refer to a primary key as "CustomerID" in one table and as "CustID" in another.
  • Dont prefix columns with their data type. This is unnecessary and makes for extra typing.

Indexes

• Use the table name as a prefix plus the first indexed column name. If creating more than one index starting with this column, use as many as necessary to uniquely identify the index.
• Always explicitly name your indexes rather than allowing SQL Server to generate names. This makes indexes easier to trace and to understand just by looking at the name.
• Samples:
  • tCustomer_CustomerID, tCustomer_CustomerName_Address, tCustomer_CustomerName_CustomerNumber

Constraints

• Use the table name as a prefix plus the constrained column name.
• Always explicitly name your constraints rather than allowing SQL Server to generate names. This makes constraints easier to trace and to understand just by looking at the name.
• Samples:
  • Primary Key: tCustomer_CustomerID
  • Foreign Key: tInvoice_CustomerID
  • Unique: tInvoice_InvoiceNumber

Views

• Prefix all views with "v". This prefix helps ensure that no keywords are used as a view name.
• Complete the name with the primary entity displayed by the view in a singular form.
• For views that merge entities, use a combined name starting with the primary table.
• Samples:
  • vCustomerDetail, vCustomerAddress, vInvoiceHeader, vInvoiceDetail, vCustomerInvoiceDetail
• Comments:
  • The prefix used here helps ensure that no keywords are used as a view name. For example, "user" is a common desired view name that is also a keyword, but "vUser" is not.
  • Although consistency in naming between tables and views allows them to be used interchangeably in accordance with ANSI standards, I prefer a clear difference provided by the prefix. This is because I do not allow direct access to either tables or views to the users of my systems (all access is provided through stored procedures that feed various reports).

Stored Procedures

• Prefix all stored procedures with "p". Complete the name with the primary table affected, then the job performed. This will group all procedures for a given table in one location alphabetically.
• Use the prefix "r" for stored procedures that directly generate a report.
• Samples:
  • pCustomerList, pCustomerSearch, pCustomerCreate, pCustomerRead, pCustomerUpdate, pCustomerDelete, pCustomerPurge
  • rCustomersByCountry, rCustomersBySales
• Comments:
  • Never prefix a stored procedure with "sp_". This will cause a performance hit against your system as SQL Server always searches the Master database for these stored procedures first.

User-Defined Functions

• Prefix all user-defined functions with "f". Add a shortened description of functionality.
• Samples:
  • fSplit, fMixedCase

Triggers

• Prefix all triggers with "tr". Add the table name and trigger type.
• Samples:
  • trCustomerInsert, trCustomerUpdate

General Notes

• Never use reserved words as a name for any database object. The SQL Server Books Online help file contains a list of reserved words under the title "Reserved Keywords (Transact-SQL)".
• Only use letters, numbers and underscores in the names of database objects. Specifically, never use a space as these can create many problems in other applications.
• Avoid extremely long names, but don't oversimplify past the point of readability. Too many acronyms makes it difficult for new developers to follow the design.
• Use mixed case rather than underscores (in most cases) to indicate word breaks . Use "pCustomerAddressCreate" instead of "pcustomer_address_create".
• Use singular names rather than plural. This is often debated - singular naming saves a character when coding.

Behind The Scenes

I was once faced with the task of "cleaning up" a database with more than 1200 tables and 2000 stored procedures. 80% of these tables and procedures were not in use and needed to be removed. A standard naming convention would have made the cleanup work much faster and would have allowed new developers to learn the system much faster as well.

In addition, a standard naming convention would have allowed for directed text searches to trace specific stored procedures and tables. This would have allowed consolidation of some redundant procedures without having to resort to a "change it and see what breaks" testing methodology.

References and Additional Reading

• Narayana Vyas Kondreddi
• Joe Celko's SQL Programming Style (a book available on Amazon.com)

About "Practical Methods"

I have written the "Practical Methods" series as a guide for database developers and administrators that are starting out with SQL Server. The articles are intended to serve as a quick reference and starting point but are not necessarily comprehensive. The articles are written for SQL Server 2005 but most will also apply to SQL Server 2000.

This information has been pulled directly from my day-to-day experience. I hope that you find it useful - Michael Lato