Skip to content

Instantly share code, notes, and snippets.

@ericthomasca

ericthomasca/db_naming_conventions.md

Last active December 16, 2024 19:03
Show Gist options
  • Save ericthomasca/31b54d64b7ba5f0142f4751b849f57fc to your computer and use it in GitHub Desktop.
Save ericthomasca/31b54d64b7ba5f0142f4751b849f57fc to your computer and use it in GitHub Desktop.
Download ZIP
Practical Methods: Naming Conventions
Raw
db_naming_conventions.md

Practical Methods: Naming Conventions

Source: SQL Server Central - Michael Lato

Summary

Everyone should establish some sort of naming convention for their SQL Server platform. It helps to ensure that developers and DBAs can easily find objects and communicate with one another. New author Michael Lato brings us the start of a series on organizing your SQL Server code with an article on 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 enhance usability. Ideally, follow a single naming convention throughout all SQL Servers in an organization.

Naming Conventions

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 helps ensure that no keywords are used as a table name (e.g., "user" is a common table name but also a keyword; "tUser" is not).
  • Never link a stored procedure to a table with the "temp" prefix to avoid accidental deletion in production.

Columns

  • Name columns according to the information they contain. Primary keys should use the table name plus the suffix "Id" and be an auto-incrementing identity column.

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.
  • Don't prefix columns with their data type.

Indexes

  • Use the table name as a prefix, followed by the first indexed column name, and the prefix "_IN" to indicate the index.
  • Always explicitly name your indexes rather than allowing SQL Server to generate names.

Samples:

  • tCustomer_CustomerId_IN, tCustomer_CustomerName_Address_IN, tCustomer_CustomerName_CustomerNumber_IN

Constraints

  • Use the table name as a prefix, followed by the constrained column name.
  • Always explicitly name your constraints rather than allowing SQL Server to generate names.

Suffixes:

  • _PK for Primary Key
  • _FK for Foreign Key
  • _UN for Unique Key

Samples:

  • Primary Key: tCustomer_CustomerId_PK
  • Foreign Key: tInvoice_CustomerId_FK
  • Unique: tInvoice_InvoiceNumber_UN

Views

  • Prefix all views with "v". This 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 helps ensure no keywords are used as view names (e.g., "user" is a common view name but "vUser" is not).
  • A clear distinction between tables and views is maintained by using the prefix, even though ANSI standards allow interchangeable use.

Stored Procedures

  • Prefix all stored procedures with "p", followed by the primary table affected, then the job performed.
  • Use the prefix "r" for stored procedures that directly generate a report.
  • Follow one of the three formats for naming procedures:
    1. If the procedure is generic and reusable, use pTableNameVerb, e.g., pVehicleGet.
    2. For project-specific procedures, use pTableNameProjectVerb, e.g., pVehicleAaxGet.
    3. For high-level system procedures, use pSystemTableNameVerb, e.g., pSystemForeignKeyGet.

Samples:

  • pCustomerList, pCustomerSearch, pCustomerCreate, pCustomerGet, pCustomerUpdate, pCustomerDelete, pCustomerPurge
  • rCustomersByCountry, rCustomersBySales

Comments:

  • Never prefix a stored procedure with "sp_". This causes a performance hit as SQL Server always searches the Master database first.

User-Defined Functions

  • Prefix all user-defined functions with "f" and 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. Refer to the SQL Server Books Online help file for a list of reserved keywords.
  • Only use letters, numbers, and underscores in the names of database objects. Never use spaces.
  • Avoid extremely long names, but don’t oversimplify past the point of readability.
  • Use mixed case rather than underscores (in most cases) to indicate word breaks. For example, use pCustomerAddressCreate instead of pcustomer_address_create.
  • Use singular names rather than plural.

Behind The Scenes

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

Additionally, a standard naming convention would have enabled text searches to trace specific stored procedures and tables, simplifying the consolidation of redundant procedures.

References and Additional Reading

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

About "Practical Methods"

I have written the "Practical Methods" series as a guide for database developers and administrators who are starting out with SQL Server. The articles are intended to serve as quick references but are not necessarily comprehensive. These articles are written for SQL Server 2005, though most apply to SQL Server 2000.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment