Database Object Naming
For customizations, it is important that tables, stored procedures, user-defined functions, and views start with USR_.
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 names should start with TR_ and CK_, respectively, and should include the custom table name within their name. 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 |
Triggers | TR_USR_<TABLENAME> | TR_USR_CONSTITUENTSPORT_UPDATE |
Check Constraints | CK_USR_<TABLENAME> | CK_USR_CONSTITUENTSPORT_VALIDSPORT |
Views | USR_V_<VIEWNAME> | USR_V_CONSTITUENTSPORT |
Names of all database objects we create (tables, functions, stored procedures, etc.) should be in upper-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.
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 |