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
Abbreviation |
Module |
cc |
Career Conversation |
cfg |
Configuration |
cl |
Catalogue |
com |
Communication (Import / Export) |
cpd |
Credits |
dbd |
Dashboards |
ab |
Assessment Builder |
ee |
Employee Evaluation |
el2 |
E-Learning v2 |
el |
e-Learning |
els |
Learning Store |
em |
Event Management / Training and Scheduling |
fais |
FAIS |
ate |
Ask The Expert/ Discussion Forum |
icn |
Icodeon |
ir |
Internal Relations / Disciplinary Actions |
jl |
Job Leveling |
jp |
Job Profiler |
leave |
Leave Management - New |
lic |
Licences |
mc |
Mentors and Coaches |
ntf |
Notifications |
org |
Organisational Structure |
pdm |
Performance Management |
pdp |
Personal Development Plan |
prc |
HR Processes |
prs |
Personnel |
pw |
Pathways |
rb |
Report Builder |
rec |
Recruitment |
rem |
Remuneration |
rp |
Resource Planning |
rpt |
Reports / Report Management |
sms |
SMS Notifications |
sr |
Salary Review |
ss |
Salary Scenario |
sty |
System Framework |
sys |
System Administration |
tal |
Talent Management - New |
sc |
Succession and Career Planning |
tM |
Talent Management - Old |
txAudit |
Auditing - Old |
wf |
Work Flows |
tr_ |
Trigger |
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
- Do not specify module prefix e.g.
- 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