Friday, January 08, 2010

Database naming standards

Via NixonCorp Team Blog:

Prefixes (all prefixes are lower case):

Example Standard What is it?
tb_Users tb_ prefix Table
UserName Pascal Case Column
up_User_Insert up_ prefix Stored Procedure
v_Users v_ prefix View
fn_UpdateUsers fn_ prefix User Function
syn_Users syn_ prefix SQL Synonym
idx_Users_001 idx_ prefix Index
@userName @ + Camel Case func, proc Parameter
@UserName @ + Pascal Case Local variable
  1. Tables are always plural (tb_Users, never tb_User)
  2. Columns are in Pascal Case (UserId, FirstName)
  3. Primary Keys are single columns whenever possible
  4. Primary Keys are named after the table (UserId)
  5. Primary Keys end with Id (UserId, not Key or Pk)
  6. Foreign Keys end with Fk (UserFk, never Id, Key or Pk)
  7. Stored Procedures never use the sp_ because this is a known SQL Server performance problem
  8. Stored Procedures are named up_NOUN_VERB such as up_User_Insert or up_User_Search or up_User_Delete, not up_Delete_User or up_DeleteUser or up_UserDelete.
  9. Triggers are named tr_TABLE_ACTION such as tb_Users_UpdateSecurity, not tb_UpdateUserSecurity
  10. Index names don’t really matter. But if we want to conform them we use idx_TABLE_TYPE_COLUMNS like idx_Users_Clustered_LastNameFirstName. If the number of columns is too long, then idx_Users_Clustered_001

There are special rules especially for cross reference tables.

  1. Names should include the parent table’s name
  2. Names should include the static term “cross”
  3. Names should include the child table’s name
  4. They always have a primary key called CrossId
  5. Many to One = tb_Groups_cross_User (in all reality, this should never happen – use One to Many).
  6. One to One = tb_User_cross_Group (singular child)
  7. One to Many = tb_User_cross_Groups (plural child)
  8. Many to Many = tb_Users_cross_Groups (all plural)

More Info:
Read my previous post Coding standard – naming of UI elements

No comments: