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:
-
A table should be named according to the entity it represents, with no abbreviations if possible. (i.e. CONSTITUENT, PHONE).
-
A custom stored procedure name begins with USR_USP_, is followed by the name of the primary table it manipulates, and ends with a description of its functionality (i.e. USR_USP_FOODBANK_CREATE).
-
A user-defined function begins with USR_UFN_, is followed by the name of the primary table it accesses, and ends with a description of its functionality (i.e. USR_UFN_FOODBANK_GETFULLNAME).
-
A view name begins with USR_V_, is followed by the name of the primary table it accesses, and ends with a description of its functionality (i.e. USR_V_FOODBANK_TRANSACTIONCOUNT).
-
A statement containing a JOIN must use a fully qualified name for fields such as TABLENAME.FIELDNAME instead of FIELDNAME.
-
An index name begins with "IX_USR_," is followed by the name of the table, and ends with the columns that the index uses.
Note: For information about how to write custom indexes for Blackbaud production tables, see Custom Indexes
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 |