Commandment 5: Prefix Custom SQL Objects

Database Object Naming

For customizations, it is important that tables, stored procedures, user-defined functions, and views start with "USR_."

Tip: Starting with version 3.0, you can specify a default prefix for database artifacts when you install the SDK. For information about this option, log in to the SDK downloads page and view the installation instructions.

Triggers and check constraints should start with "TR_" and "CK_," respectively, and include the custom table name. Indexes should start with "IX_" and include the custom table name or Blackbaud production table. Prefixing database objects with "USR_" is key to avoid potential naming collisions with future features delivered as part of the product. Each catalog spec may include a number of database objects, but typically you are dealing with stored procedures, tables and user-defined functions. Per each database object, here is the general naming convention:

Database Object Naming Example
Table USR_<TABLENAME> USR_CONSTITUENTSPORT
Stored Procedure USR_USP_<SP NAME> USR_USP_CONSTITUENTSPORT
User-defined Function USR_UFN_<FUNCTION NAME> USR_UFN_CONSTITENTSPORT_GETLIST
Trigger TR_USR_<TABLENAME> TR_USR_CONSTITUENTSPORT_UPDATE
Check Constraint CK_USR_<TABLENAME> CK_USR_CONSTITUENTSPORT_VALIDSPORT
View USR_V_<VIEWNAME> USR_V_CONSTITUENTSPORT
Index IX_USR_<TABLENAME>_<KEY COLUMN 1>_<KEY COLUMN 2>_<...> IX_USR_FINANCIALTRANSACTION_BASEAMOUNT_DATE

Tip: We also recommend that you include the "USR_" prefix on field names in batch extension specs. This avoids naming conflicts with existing fields in batch specs and with future fields that Blackbaud may add to batch specs. Starting with version 4.0, placeholder field names in batch extension specs include the "USR_" prefix by default. If your batch extension fields do not include the "USR_" prefix and Blackbaud adds fields with the same names, conflicts will occur and you will need to update the field names on the batch extension.

Names of all database objects (tables, functions, stored procedures, etc.) should be in upper-case.

In Transact-SQL statements, Transact-SQL keywords should always be lower-case.

When referenced in stored procedures, functions, etc., database objects should always be referenced using the database owner, typically dbo (i.e. select FIRSTNAME, KEYNAME, dbo.USP_CONSTITUENT_GETFULLNAME(ID) as FULLNAME from dbo.CONSTITUENT).

The individual database objects should be named according to these rules:

Specific Spec Naming

Certain catalog specs contain database objects that have additional naming conventions. The general database object naming convention applies, but keep the spec's naming convention as well.

Spec Type Naming Example
Add Data Form USR_USP_DATAFORMTEMPLATE_ADD_<ENTITY> USR_USP_DATAFORMTEMPLATE_ADD_CONSTITUENTSPORT
Edit Data Form (Load) USR_USP_DATAFORMTEMPLATE_EDITLOAD_<ENTITY> USR_USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTSPORT
Edit Data Form (Save) USR_USP_DATAFORMTEMPLATE_EDIT_<ENTITY> USR_USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTSPORT
View Data Form USR_USP_DATAFORMTEMPLATE_VIEW_<ENTITY> USR_USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSPORT
Data List USR_USP_DATALIST_<ENTITY> USR_USP_DATALIST_CONSTITUENTSPORT
Search List USR_USP_SEARCH_<ENTITY> USR_USP_SEARCH_CONSTITUENTSPORT
Query USR_V_QUERY_<ENTITY> USR_V_QUERY_CONSTITUENTSPORT
Smart Query USR_UFN_SMARTYQUERY_<ENTITY> USR_UFN_SMARTQUERY_CONSTITUENTSPORT
Merge Task USR_USP_MERGETASK_<ENTITY> USR_USP_MERGETASK_CONSTITUENTSPORT
Global Change USR_USP_GLOBALCHANGE_<ENTITYACTION> USR_USP_GLOBALCHANGE_ADDCONSTITUENTSPORT
Smart Field USR_USP_SMARTFIELD_<ENTITYACTION> USR_USP_SMARTFIELD_CONSTITUENTSPORTCOUNT
Simple Data List USR_USP_SIMPLEDATALIST_<ENTITY> USR_USP_SIMPLEDATALIST_SPORT
Translation Function USR_UFN_TRANSLATIONFUNCTION_<ENTITY> USR_UFN_TRANSLATIONFUNCTION_SPORT
Record Operation USR_USP_<ENTITY>_<ACTION> USR_USP_CONSTITUENTSPORT_DELETE
Name Format Function USR_UFN_NAMEFORMAT_<ENTITY> USR_UFN_NAMEFORMAT_SPORTNICKNAME
Country Address Format USR_UFN_ADDRESSFORMAT_<COUNTRY/NAME> USR_UFN_ADDRESSFORMAT_USCUSTOM
Report (sp) USR_USP_REPORT_<ENTITYACTION> USR_USP_REPORT_CONSTITUENT_GETSPORTS