Database Customization

Owned by Greg Walsh
Sept 30, 2021
9 min read

SQL Naming Standards is a highly charged topic. The debate on the use of a 2 or 3 character prefix denoting the type of database object continues to this day. To make matters worse there are many so called SQL Naming Standards out there and not one of them has been adopted by Microsoft.
The objective of adopting a good SQL Naming Standard for database objects is to limit ambiguity and to aid in the ability of developers including yourself to intuit the nature of an object. There is one key point, however, that both sides can agree upon: regardless of the decisions made, be consistent with the selected convention.

EngageIP SQL Naming Standard

First some don'ts

The following are some things to avoid when naming objects in the database:

  • Avoid using object names that require quotes.

  • Avoid names with white spaces or special characters (especially dashes).  For example, naming a table "Order Detail" or "Order-Detail", instead of "OrderDetail" or "order_detail" can lead to hours of aggravated debugging.

  • Avoid reserved keywords for object names. Naming a table or field "User" can also lead to aggravated hours of debugging.

  • Avoid using data types as object names. Naming a field "Date" can also lead to aggravated hours of debugging.

  • Avoid abbreviations since SQL Server allows 128 Characters for a database object name.

Some of the Object types and their acronym prefixes used in this page are:

  • AK = Alternate Key UNIQUE Constraint

  • CK = CHECK Constraint

  • DF = DEFAULT (constraint or stand-alone)

  • FK = FOREIGN KEY Constraint

  • IX = Non Unique Index

  • IXU = Unique Index

  • PK = PRIMARY KEY Constraint

  • PKC = Clustered PRIMARY KEY

  • TR = DML Trigger

  • UDF = User Defined Function

  • USP = User Stored Procedure

  • VIEW = View

  • XP = Extended Stored Procedure

Other acronym prefixes will be discussed in their related sections.

All object names should use Pascal Case: The first letter of each concatenated word is capitalized. For example: StatementDetails and InvoiceRender.

SQL Objects

Tables

Tables are the basic storage object and do not require any prefix to identify them as such. Table names should be singular not plural. EngageIP uses both in some cases for historical reasons. The majority of the EngageIP tables use singular name notation as in:

  • AuditLog

  • ContactPoint

  • InventoryItemStatusType

Table names should normally be nouns and should not normally have verbs in the name.

Any non core tables should begin with an underscore ("_").

Any custom table needs to be identified by a leading underscore and the customer name. For example, _CustomerName_TableName.

Views

Naming standards for database views is by far the most contentious of all the SQL naming standards. A view can be used to perform so many different tasks

  • Security

    • to filter out rows from another department that the user should not see with a where clause.

    • to filter out columns like salary that the user should not see by not including those fields in the definition.

  • Join two or more tables or even join additional views.

  • Report query definition.

EngageIP uses a prefix of "View" with no trailing underscore followed by a unique name. The general naming convention is: View<UniqueName>.

Some concrete examples are:

  • ViewPaymentGroupByUser

  • ViewSearchUserAttributeProfileAnswer

Do not differentiate between View & Materialized View with a prefix as view names will be in code. Changing a view to a materialized view and changing the name will break the code.

There are two points of view on naming database views:

  • Logical naming - uses a descriptive name for the function of the view.

  • Physical naming - list the table names separated by underscores in the name.

As a view acts similarly to a table, it should be named accordingly with nouns.  It should not normally have verbs in the name. The only real difference, besides the storage of the data, between a table and a view is that a view may not be update-able. By prefixing the view name with the word "View" it can help developers to immediately know they are working with a view and not a regular database table.

If physical naming is used and the view combines entities with a join condition or where clause, be sure to combine the names of the entities separated by underscores ("_") that are joined in the name of the view. There are many views in EngageIP where this is impractical as the number of joins is in the dozens.

Any views created specifically for an EnagageIP report should use the report name and end with the suffix "Report". For example:

  • ViewAgedInvoiceDetailReport

  • ViewBatchPaymentReport

Any views that are added in addition to the standard EngageIP deployment schema should begin with an underscore ("_") prefix. For example:

  • _ViewPricePlanTotal

  • _ViewTierSyncDuplicateServices

Any custom view needs to be identified by a leading underscore and customer name as in  "_<CustomerName>_View<UniqueName>"

An indexed view should begin with an additional prefix of "ivw_" at the beginning of the name. For example, "ivw_ViewAccountPayableReportPerUser".

Scripts

EngageIP includes several scripts as part of the core product deployment. These script names use the prefix "us_" to denote that they are a user script. The name format is  us_<ScriptName>  where <ScriptName> is a self documenting name of the script. As a script performs some task or function on some object or objects the script name should have both a verb and a noun in the name. For example:

  • us_LS_Adjust_ConnectorIndexes

  • us_LS_Rename_PK_ClusteredIndexes

Any scripts that are created for a customer deployment of EngageIP should have a leading underscore, "_", and the customer name in the script name. For example, "_CustomerName_us_ScriptName".

Stored Procedures

EngageIP includes several stored procedures as part of the core product deployment. These script names use the prefix "usp_" to denote that they are user stored procedures. The name format is  "usp_<StoredProcedureName>"  where <StoredProcedureName> is a self documenting name of the stored procedure. A stored procedure performs some task and thus the procedure name should be descriptive of that task. Stored procedure names should have both a verb and a noun in the name.  For example:

  • usp_GetInvoice

  • usp_LS_ReGenStatistics.

Stored procedures should have at least one consistent prefix like "usp_". For historical reasons EngageIP has a mix of naming conventions for some older stored procedures.

For example:

  • GetOwnerTree - has no prefix

  • sp_UDRRatePlanCompare - "sp" is a poor prefix choice as it should only be used by Microsoft

  • Support_ExportDateAnalysis - "Support" prefix denotes a stored procedure used during support activities but is not prefixed correctly

  • usp_LS_RebuildIndexes - "usp_" is using the recommended prefix naming convention

Any non core EngageIP stored procedures should begin with an underscore ("_").

Any custom stored procedures should have a leading underscore, "_", and the customer name in the name. For example,  "_CustomerName_usp_StoredProcedureName"

Functions

EngageIP includes several functions as part of the core product deployment. These function names use the prefix "udf_" to denote that they are user defined functions. The name format is  "udf_<FunctionName>"  where <FunctionName> is a self documenting name of the function. A function performs some task and the name of the function should be descriptive of that task. Function names will normally have both a verb and a noun in the name. For example, udf_GetUsersBalance.

It is important that function names should also differentiate between scalar, inline and table level functions. Forethought should be used as function names will be in code and changing a scalar or inline function to a table level function and changing the name will break the code as well as requiring a different calling method.

The following is a list of different function types and their recommended prefixes:

  • Scalar - "udfs_"

  • Table - "udft_"

  • Prefix - "udfa_"

  • Other - "udf_"

Due to historical reasons EngageIP has a mix of naming conventions for some older functions.

For example:

  • Extract - is missing the recommended prefix

  • fnExtractSubstring - has a prefix but not the recommended prefix

  • func_GetDateOnly - has a prefix but not the recommended prefix

  • udf_GetUserInitials - has the recommended prefix

Any non core EngageIP functions should begin with an underscore ("_").

Any custom functions should have a leading underscore, "_", and the customer name in the name. For example, "_CustomerName_udf_FunctionName"

Foreign Key Constraints

Foreign keys used in table definitions should use the prefix "fk_" followed by underscore seperated table names between the two tables invovled in the foreign key relationship. Specifically, the format is "fk_<CurrentTableName>_<ReferencedTableName>.

For example:

  • FK_Contact_ContactType

  • FK_StatementDetails_Statement

SQL Agent Jobs

EngageIP includes SQL Agent Jobs as part of the core product deployment. These SQL Agent Job names use the prefix "LS_" to denote that they are created by LogiSense. The name format is  "LS_<SQL Agent Job name>"  where <SQL Agent Job name> is a self documenting name of the SQL Agent Job. SQL Agent Jobs perform some task and the name should be descriptive of that task.

For example:

  • LS_EngageIP_ArchiveUDR

  • LS_EngageIP_RegenStatistics_MediumSize

There are no naming conventions for non core or custom SQL Agent Jobs as their storage location unambiguously identifies them as being SQL Agent Jobs.

Non Queryable objects

Non queryable objects can normally be easily renamed with little or no issues. An Index name however can be used in a query hint and constraint names can be searched for in error messages. Renaming the index or constraint names could break something so caution should be used.

Examples of non queryable objects:

  • Indexes

  • Constraints

  • Triggers

Indexes do have a recommended naming convention in EngageIP outlined in a section below while contstraints and triggers do not.

Queryable objects

Care has to be used with queryable objects as different functionality can be added to perform some modified function. The name may no longer be accurate and changing the name may break the code that depends on it.

Examples of queryable objects:

  • Table names

  • User stored procedures

  • User defined functions

  • View names

Core vs. non-Core Objects

EngageIP includes numerous objects as part of the core product deployment.  These naming conventions are detailed in this page.  It is necessary to differentiate between the following non core object types with regards to additional naming conventions that should be applied:

  • Logisense provided custom objects - Starts with the prefix "_LS_".  For example, "_LS_IX_StatementDetails_PackageIDExtra"

  • Customer supplied custom objects - Starts with the prefix "_XY_" where "XY" is a two character abreviation for the customer name. For example, "_AC_IX_UDR_UserServiceID_UDRBillerID" for Acme Corp.

  • Temporary objects - Starts with two underscore characters followed by a two character abbreviation for the author followed by an underscore. For example, if John Smith creates an object the prefix would be "__JS_".  A optional "_<date>" suffix for temporary objects can also be used to help identify the history of the temporary object.

  • EngageIP Support team tables use the following convention: "sup_bak_TS<ticket number>_<tablename>_<date>". For example, "sup_bak_TS1234_StatementDetails_20170125" for backing up the StatementDetails table, for ticket TS-1234 on Jan 25th, 2017.

The date format to be used is "yyyymmdd" where "yyyy" is a 4 digit year, "mm" is a two digit month, and "dd" is a two digit day.

Indexes

Indexes are non queryable objects and should be relatively easy to convert to any naming standard.

There are many different types of indexes in SQL Server and many different naming standards. The recommended naming convention is as follows:

  • PK_ Primary Key Constraint Non Clustered as in: PK_TableName_ColumnName/s

  • PKC_ Clustered Primary Key Constraint as in: PKC_TableName_ColumnName/s

  • IXUC_ Clustered Unique Index as in: IXUC_TableName_ColumnName/s

  • IXC_ Clustered non Unique Index as in: IXC_TableName_ColumnName/s

  • IXU_ Unique non Clustered Index as in: IXU_TableName_ColumnName/s

  • IX_ Non Unique non Clustered  as in: IX_TableName_ColumnName/s

  • AK_ Alternate Key Unique Constraint  as in: AK_TableName_ColumnName/s

  • IXF_ Filtered Index as in: IXF_TableName_ColumnName/s

  • IXUF_ Unique Filtered Index as in: IXF_TableName_ColumnName/s

  • IX***_inc Index with Include Fields  as in: IX_TableName_ColumnName/s_inc

Field name conventions

EngageIP core tables place the Clustered Primary Key on the ID field of the table. There are a few exceptions to that rule with respect to date fields. Date fields are named with "Date" as a suffix". For example, InvoiceDate instead of InvoicedOn or DateInvoiced. Another example is CreationDate instead of CreatedOn or DateCreated.

Storage naming standards

The Microsoft standard for storage naming conventions expects this format:

<drive letter>:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER2016\MSSQL\DATA

The problem with the Microsoft recommendation is that it includes a database version in the path.  As the database is upgraded path names either have to change to be accurate or old path names will incorrectly identify the database version being used.  To avoid that problem Logisense uses the convention

  • <drive letter>:\Logisense\Client\data where <drive letter> is the drive partition letter used for this path.   For example, "E:\Logisense\Client\data".

This will be consistent on shared servers as well.

EngageIP uses the following filegroups names for standard deployments:

  • EIPDATAFG - main data file group

  • EIPNDXFG - main data indexes file group

  • LOGDATAFG - log data file group

  • UDRDATAFG - UDR Data file group

  • UDRNDXFG - UDR Data indexes file group

Datafiles should have the naming convention "<DatabaseName>_<FileGroupName>_F<FileNumber>".  In the case of a standard EngageIP deployment the files would look like this:

  • EngageIP_EIPDATAFG_F1

  • EngageIP_EIPNDXFG_F1

  • EngageIP_LOGDATAFG_F1

  • EngageIP_UDRDATAFG_F1

  • EngageIP_UDRDATAFG_F2

  • EngageIP_UDRNDXFG_F1

  • EngageIP_UDRNDXFG_F2

The EngageIP storage naming standard has a mix of naming conventions due to historical reasons.