Naming conventions

General

• Decide per module if abbreviation (e.g. prs for Personnel module) or full name (e.g. leave) will be used for database Objects.
• Do not use spaces in the names of database objects.
• Avoid using ntext, text, and image data types in new development work. Use nvarchar (max), varchar (max), and varbinary (max) instead.

Note: The parent / grouping determines the module the data is stored on E.g. EmployeePDPs (an employee’s PDP’s) vs. pdpPeriod (a PDP’s periods)

Table 1 : Database Module Abbreviations

Tables

• A table name must always be prefixed with the module name abbreviation (see above).
• A database table name must always be plural
  • prsEmployees – There will most likely be more than one employee in the system
  • LeaveGroupTypes – Each Leave Group can have one or more Leave Type
  • eeImports – Only one import can run at a time
  • pdpStatuses – There are multiple statuses for the module
• A column name must be the shortest descriptive name possible
  • Do not specify module prefix e.g. 

    Correct column name EmployeeId

    Incorrect column name prsEmployeeId
    

  • Exceptional case: If more than one column in the same table are the “same” e.g. CategoryId, specify module prefix e.g. cpdCategoryId, pdpCategoryId
• A column name must refer to a single and not multiple instances
  • Use UnitId instead of UnitsId
• Rather use varchar(max) instead of text or varchar(8000) types for string columns where applicable
• Each table that has a single identity column must also have a clustered primary key with the following naming convention:
  • PK_{TableName}_{IdentityColumn}
• A foreign key constraint name must be in the following format:
  • FK_{TableName}_{Column1}

Views

• A View’s name must follow the same convention as table names (add View at end of name)
• Use prsTerminatedEmployeesView instead of viewTerminatedEmployees
• naming: {prefix}{Description of the data resturned}View

User-Defined Functions

• A User-Defined function's name must follow the same convention as table names.
• A user defined function must be prefixed with the module (do not add fnc prefix)
• Use el2SelectScholarshipManagerNotificationDays instead of fncSelectScholarshipManagerNotificationDays
• General user defined functions (module-unspecific) can be the description of the output
• Use Split instead of fncSplit
• Use CleanHtmlTags instead of fncCleanHTMLTags

Stored Procedures

• A stored procedure name must always be prefixed with the module.
• A stored procedure must indicate it’s intention by using a keyword on what action will be performed
  • Select
  • InsertUpdate
  • Insert
  • Update
  • Delete
  • Check/ Verify
  • Copy
  • Archive
  • Reset
  • Apply
• Examples
  • prsSelectEmployeesAll
  • prsSelectEmployeesList - paging
  • prsSelectEmployee - single
  • prsInsertUpdateEmployee
  • prsDeleteEmployee
  • prsCheckEmployeeIDNumber
  • styResetUserPassword
  • pdmCopyContract

Temporary Tables

• Single use temp table: #{Descriptive table name}
• Global use temp table: ##{Descriptive table name}
• Variable temp table @{Descriptive table name}

Common Table Expressions (CTE's)

• CTE table names are declared with the prefix cte.

Indexes

• A non-clustered index name must be in the following format:
• IX_{TableName}_{Column1}_{Column2}
• Indexes have a maximum size of 900 or 1700 depending on the index type and SQL version. Do not create a non-clustered index on a column with a max length of more than 500.
• Always check with the Database Administrator whether indexes should be created during development. Assume that indexes will always be created.

Constraints

• A default constraint name must be in the following format:
• DF_{TableName}_{Column1}
• A unique constraint name must be in the following format:
• UQ_{TableName}_{Column1}_{Column2}
• A check constraint name must be in the following format:
• CK_{TableName}_{Column1}_{Column2}
• Columns with Default value constraint should not allow NULLs.

TODO: Add a link to main page for each section