Sql Standards

SQL CODING STANDARDS (Ver2)

Original: January 11, 2010
Last Modified: March 5, 2010

Modifications
* Modification are in highlighted
* 2010/03/05 Modified the table for Naming Conventions, changed the abbreviation for “Subscription” to “Subs”
Subscription | Subs |



Tables
Naming Convention For Table And Column Names
* When naming tables, express the name in thesingular form. For example, use Employee instead of Employees.
* Avoid using excessively long names for tables and columns. Instead use the abbreviations from the table listed below. However, the final name should be easily readable and should not look like an alphabet soup:
Name | Abbreviation |
Action | Action |
Activity | Activity |
Administration | Admin |
Application | App |Archived | Archived |
Asset | Asset |
Authorization | Authz |
Business | Biz |
Category | Category |
Checker | Checker |
Code | Code |
Company | Company |
Configuration | Config |
Contact | Contact |
Country | Country |
Customer | Cust |
Detail | Detail |
Display | Display |
Encrypter | Encrypter |
Enrollment | Enroll |
Executed | Executed |
Format | Format |
Group| Grp |
HeartBeat | HeartBeat |
History | History |
Identification | Id |
Installer | Installer |
Manager | Mngr |
Menu | Menu |
Merchant | Merchant |
Number | Num |
Organization | Org |
Payment | Payment |
Periodicity | Periodicity |
Program | Prg |
Qualys | Qualys |
Reference | Ref |
Registration | Reg |
Relationship | Relation |
Report | Report |
Scan | Scan|
Scanner | Scanner |
Scheduled | Scheduled |
State | State |
Store | Store |
Submission | Submission |
Subscription | Subs |
Summary | Summary |
Template | Template |
TimeZone | TimeZone |
Transaction | TRX |
Type | Type |
Updater | Updater |
Uploader | Uploader |
* When naming columns of tables, do not repeat the table name except for the primary and foreign keys;for example, avoid having a field called EmployeeLastName in a table called Employee.
* Add the suffix “_PK” for primary keys
For example, if the primary key field of the Org is Id then the field should be named Org_Id_PK
* Add the suffix “_FK” for foreign keys
For example, if the Org table has a field “State_Id” that is a foreign key to the “State” table then the field should be namedState_Id_FK
* Do not incorporate the data type in the name of a column. This will reduce the amount of work needed should it become necessary to change the data type later.
Mandatory Fields In Each table
Every table is to have the following 3 fields (column) as the last 3 columns in the same order:
,[Archived] [bit] NULL -- 1: if the record is deleted, 0 if the record is notdeleted.
,[LastSetBy] [nvarchar](50) NULL –- Name of the user who last modified/created the record.
,[LastSetDate] [datetime] NULL -- Date Time stamp when the record was last modified/created.

Miscellaneous
* Minimize the use of abbreviations. If abbreviations are used, be consistent in their use. An abbreviation should have only one meaning and likewise, each abbreviated word shouldhave only one abbreviation. For example, if using min to abbreviate minimum, do so everywhere and do not later use it to abbreviate minute.
* When naming functions, include a description of the value being returned, such as GetCurrentWindowName().
* File and folder names, like procedure names, should accurately describe what purpose they serve.
* Avoid reusing names for differentelements, such as a routine called ProcessSales() and a variable called iProcessSales.
* Avoid homonyms when naming elements to prevent confusion during code reviews, such as write and right.
* When naming elements, avoid using commonly misspelled words. Also, be aware of differences that exist between American and British English, such as color/colour and check/cheque. CHOOSE AMERICAN...