Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 12, 2000

Standards Part 2 - Naming Objects

By Steve Jones

Introduction

Everyone has naming standards in their company. If they do not, they should. It makes everything easier for all employees. Developers learned long ago that having naming standards in place makes it easier to train new developers, troubleshoot code, and reduces errors. The same holds true for SQL Servers. If something is standardized, then you get some amount of information just from the name. That may help you find an object quicker, understand the object's purpose, or just keep everything more organized.

When I started managing SQL Servers, I quickly needed to develop standards to ensure that other developers and I could work with each others' code. I searched and adapted a set of standards from a few different sources to meet my needs. What I had hoped to find was a set of standards that someone else had tried and just use them. So, here are the ones I have come up with. Please feel free to use these in your company or change them to meet your needs.

The following are standards that I have developed and evolved over the years. Feel free to use them if you like or come up with your own.

Database Devices or files:

Devices no longer apply to SQL Server v7.0 and above, but there are still physical files which should follow this convention.

Database devices and files shall be named for the name of the database that is associated with them. The format for the file name should be the following:

<database>_<purpose><sequence>.<extension>

There are three types of files in SQL Server 7 and above: data files, system files, and log files. By default, system and data files are combined into the first data file. If there is more than a single data file, the sequence should be a numerical differentiator between the files. The extensions are determined as follows:

First data file: .mdf

Subsequent data files:.ndf

Log files: .ldf

The name shall be in proper case, except where an acronym is being used.

Filename Examples:

  • Iqd_dev_data.mdf
  • Master_data.mdf
  • QA_log.ldf
  • DBA_Sys.mdf
  • DBA_Data.ndf

Backup Devices:

Backup devices are to be named in the following manner:

<Server>_<database>_<timestamp - Optional>.<type>

where

server: name of the server. If a central backup server is ever used, then this will be needed.

Database: name of the database in the same format as the database is named (same case, etc).

Time: timestamp for the backup. Optional.

Type: .bak for full backups, .dif for differentials, .trn for incrementals.

Backup File Examples:

  • Morpheus_master.BAK full backup of Morpheus..master
  • ProdSQL_Dynamics.dif differential backup of the Dynamics database on the ProdSQL server.
  • ProdSQL_Dynamics.trn transaction log backup (incremental) of the Dynamics database on the ProdSQL server.

Databases:

User databases are to be named for the application or purpose they server. Proper case for database names is suggested, but many applications may create their own database using Upper case.

Example:

  • Training
  • GMSM
  • master
  • QA

Logins:

Logins shall consist of the first letter of the person's first name and the full last name. If there are collisions, then the first initial of first name, first initial of middle name can be used. If this is not sufficient, then begin spelling out the first name and then the middle name until the collision is resolved. All logins shall consist of lower case letters. The exceptions to this are any system level or administrative logins.

Login Examples:

  • sjones
  • bsmith
  • jqpublic
  • Accounting

Users:

Users in each database should be named the same as the matching login. At this time, no reason has been given to deviate from this for any reason.

Tables:

Tables should be named for the type of data that they are holding in accordance with the business lexicon used by the company. This prevents developers from confusing or being confused when working with business people. Abbreviations for words should conform to the list of abbreviations that has been declared the standard for the company. Tables that link other tables together should have a suffix of Lx, where x is a numerical indicator. This allows multiple independent links between tables. Lookup tables that contain the PK and another duplicate field in them to speed lookups should be suffixed by a _lu. Proper case names should be used for names.

Table Name Examples:

  • Member
  • Customer
  • Product
  • Product_lu

Columns:

Columns shall be named with words describing their purpose.  Abbreviations should be used where possible and in accordance with the standard abbreviation list. Proper case all words in the name except acronyms which should be upper case.. Underscores shall be added for those columns needing to designate units.  The designations for units shall conform to the engineering standards.

Column Name Examples:

  • EmpID
  • FrstNm
  • LastNm
  • Velocity_mpm
  • Mass_kg
  • ProdSKU

Indexes:

Indexes shall be named for the table they are attached to and the purpose of the index. All letters shall be lower in case. Primary keys shall have a suffix of _PK. Foreign keys shall have a suffix of _FKx where x is a number. The number shall simply be incremental. Clustered indexes shall have a suffix of _IDX. All other indexes shall have a suffix of _NDXx where x is an incremental number. Only one suffix per index shall be appended. The application of the appropriate suffix shall follow the following hierarchy: primary key, clustered index, foreign key, other index. For example an index that is both a primary key and is clustered shall have a suffix of _PK.

Example:

  • employee_PK
  • customer_IDX
  • employee_FK1
  • employee_NDX1
  • employee_NDX2

Triggers:

Triggers shall be named by the table they are for and also for the type of trigger. All letters shall be lower in case. The purpose of the trigger shall be the prefix to the name. All triggers shall start with the letter t, a letter(s) designating the type, an underscore, and the table name. The type shall be designated as i = insert ,u = update ,d = delete.

Example:

  • ti_employee
  • tiu_employee
  • td_employee
  • tid_customer
  • Defaults:

    Defaults shall be named by a df and a description of what the default does. All words should be run together and not separated by underscores. The description should be proper case

    Example:

  • DfZero
  • DfOneDollar
  • dfGetDate
  • Rules:

    Rules shall be named by an r_ and a description of what the rule does. All words should be run together and not be separated by an underscores. Abbreviations and proper case should be used.

    Rule Examples:

    • r_onlynumbers
    • r_nospaces

    User Defined Datatypes:

    User defined datatypes should not be used at this time. The potential to limit flexibility and cause a large maintenance issue is too great. Until the IT department modifies this section, User Defined Datatypes should not be used.

    Stored Procedures:

    System level stored procedures shall be named by sp__ (two underscores) and a description of what the stored procedure does.

    All application level stored procedures shall follow a set prefix in place of the sp with a description of what the stored procedure does. The following standards are in place for these types of procedures:

    • spIns<table name> - Insert procedure for all columns for the table, except identity column.
    • spDel<table name) - requires primary key and removes the specified row from the table
    • spSel<table name> - Returns data from a single table or view. Takes the primary key as a parameter (optional). If sent, then the matching row is returned. If no parameter sent, then all rows are returned.
    • spUpd<table name> - takes primary key as parameters and then all columns in order that they appear in the table. If a NULL is sent for a parameter, then that column is not updated.
    • spGet<description> - select procedure returning data from joined tables
    • spTot<description> - returns an aggregate of some type. 
    • spSet<table><column> - sets a specific column value. A specialized procedure to set a value for a specific business purpose.
    • spInc<table><column> - increments the column value
    • spFix<prupose> - procedure to scrub or correct data that may be incorrect.
    • spSend<purpose> - Stored procedure that sends a message to someone. This could be email, broadcast, http post, etc.
    • dbsp<purpose> - DBA stored procedure to perform administrative or maintenance tasks. NOT FOR USE by applications. Permissions are not necessarily set for non-sa level logins on objects used in these procedures.

    Abbreviations should be used where appropriate. A list of abbreviations is available at: abbreviations.xls

    Example:

    • sp__LoadData
    • spChkTime
    • spGetEmpRpt
    • spInsCustomer
    • spDelOrder

    Alerts:

    Alerts shall be named with the database in UPPER CASE, a description of what the alert is for, and what level of alert it is.

    Alert Example:

    • CUSTOMER Fatal Errors 19

    Tasks and Jobs:

    Tasks shall be named with the database, the frequency or time (in 24hr format) a description of what the task is, the name of the procedure, or the name of the DTS task being run.

    Task Example:

    • ALL DATABASES Daily 0100 synch of development server Admin
    • PRODUCTION Sun 0400 DBCC checktable
    • Dynamics MTWRF 0500 Import_Sales_Flat_File

    Conclusions

    I think standards are very important to having a well organized development team and encourage you to install some if you do not have them. If you have standards, then be sure they are enforced and that they grow and change as needed. For other points of view on this topic, both Michael Hotek and Brian Knight have standards articles as well on Swynk.



    MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM