Table and Column Naming Conventions
Like any complex system, netFORUM would be confusing without standard naming conventions. Abila recommends the following naming conventions to standardize the names of database tables and columns. Please follow these.
Database Table Names
Three-character Column Prefix
Every netFORUM table has a certain three-character column prefix. In netFORUM, this is stored in [md_table].[mdt_prefix]. This three-character column prefix is unique across all other tables in netFORUM (with the exception of a table's sibling extender table which uses the same three-character column prefix). Example, the co_customer (and co_customer_ext) tables use cst and the ac_invoice (and ac_invoice_ext) table use inv.
There is only one rule regarding the choice of a three-character column prefix, but this is an inviolable rule:
If the table is a baseline table added by Avectra to the core netFORUM product for all clients, then there must be no numbers in the prefix; if the table is added to a client database as a custom table, by either the client or an Avectra employee, then the prefix must have at least one number. This rule ensures that Avectra will never use a three-character column prefix already in use by a client, and vice versa.
There is one other rule: please use only letters and numbers and make it all lowercase.
Examples:
Correct for baseline netFORUM: cst, inv, acd, phn.
Correct for custom tables: g01, b01, b02, b10.
Table Name
Baseline tables should have a two-character module prefix (for example, co_ for CRM/Core; ev_ for Events; ac_ for Accounting, and so on).
Table names should be singular and not contain plurals (e.g. co_individual, not co_individuals. All letters should be lowercase.
Cross-Reference Table Names
Use _x_ to indicate a cross-reference table between tables. (e.g. ev_event_faculty_x_co_document). If cross-reference tables have the same module prefix, then the 2nd module prefix can be dropped (e.g. co_customer_x_address rather than co_customer_x_co_address). For more information, see Cross-Reference Table.
Client-specific Tables
Client-specific database objects (tables, views, stored procedures) should be prefixed with client_abbreviation so it's easy to group them together. EG, client_abc_co_business_code.
Variations
A number of people like to put the three-character column prefix in the actual name of the table. Here is a variation on table naming practiced by a netFORUM client.
Assuming the client abbreviation is xyz then they have two tables, named as follows:
- client_xyz_a14_cme_topic_code
- client_xyz_a11_cme_activity
To create a join table between the two would it be named:
- client_xyz_a15_cme_activity_x_cme_topic_code
This naming convention sensibly omits the three-character column prefixes (a14 and a11) of the "main" tables from the name of the cross-reference table, instead putting in that table's own three-character column prefix of a15.
Override
Framework tables that contain override data allow users to personalize standard data for themselves.
Override tables are indicated by the letter o in the table name:
- md_dynamic_form_panel_o_user
- md_dynamic_profile_detail_o_user
Column Names
Avoid putting trivial articles such as "the", "of", "a" in a column name:
- Good: a87_team_lead_flag
- Bad: a87_lead_of_a_team_flag
Column Data Type
Always use a netFORUM User-defined data type (UDT) for a column wherever possible. There is a UDT for almost any data type except for an ordinary free entry nvarchar column. In this case, use unicode types (nchar, nvarchar, ntext) to properly support international characters sets. Do not use char or varchar except in rare circumstances.
Naming Foreign Keys
When creating foreign keys, try to use the parent column name as part of the foreign key column name (for example, cxa_key (parent key) -->> cst_cxa_key (foreign key)).
Naming Extender Columns
Any column in an extender table must have the suffix _ext at the end, (for example, ind_completed_survey_flag_ext). This is done both to make the extender column stand out from baseline columns and to prevent the possibility of having a baseline and extender column with the same name.
Column Names for Certain Data Types
The following rules are used to easily designate the data type and/or purpose of a column within the column name. See Data type for guidance on how to choose the column's appropriate data type.
- HTML - Any columns that store HTML tags in them should have the word html in the column name, eg adr_mailing_label_html.
- Dates - Any columns that are av_date() or av_date_small() must have the word date in the column name, eg, mbr_expire_date.
- Keys - Any columns that are av_key (uniqueidentifier) data type must have key in the name. The primary column is generated automatically in md_create_table, and FK columns that join to columns of av_key() data type will be named as described above and in other parts of this document.
- Checkbox - For columns that you want to depict as a checkbox on a form, you will make the data type of the column be av_flag. If the column is an av_flag data type, then include the word flag in the column name and be sure to set a Default Value of 0 or 1 and make the column non-NULLable. For example, cst_no_web_flag. By convention, the default value will usually be zero as we generally have checkboxes default to unchecked.
- Year - If the column will hold a calendar year, such as “start year” or “year founded”, then the recommended practice is to make the data type be av_integer. Next, add a check constraint on the column to ensure that users do not enter an implausible year (eg 243 or 8765). In the netFORUM column definition for the column, you will enter an input mask of 9999 to ensure that the user can enter at most 4 numbers. The check constraint will look like this, assuming you require that all years be between 1900 and 2100:
([h01_year] >= 1900 AND [h01_year] <= 2100)
- Denormalized - If the column is a denormalized column whose value comes from another table/column, then end the column name with the suffix _dn to designate this. For example, cst_fax_number_complete_dn. In general, netFORUM data should be normalized except in rare circumstances.
- Formula - If the column uses a Formula, then include the suffix _cp in the column name to designate that the column is "computed." For example, the column cst_name_cp is a computed column that combines first and last names.
Column Description
The Column Description ultimately goes into mdc_description which end users see in netFORUM, in particular in the Query Designer.
Observe the following conventions:
- Avoid abbreviation. Spell out the word. Example: "Expiration Date" not "Exp Date" or "Expiration Dt".
- Use initial capital letters except for "minor" words like a, to, the, etc. Example: "Approval Member Status" not "Approval member status" or "approval member status".
Module Prefix
Every baseline database table in netFORUM starts with a two-character module prefix. The count shown below is from version 2007.01 and is approximate. This count is for general understanding and might not be exact.
| Code | Module | Count |
|---|---|---|
| ab | Abstract | 8 |
| ac | Accounting | 59 |
| ad | Advertising | 24 |
| am | Accreditation | 22 |
| aw | Awards | 18 |
| ce | Certification | 37 |
| cm | AMS Content Management | 3 |
| co | Core/CRM | 116 |
| cs | Case Management | 12 |
| dg | Demographics | 3 |
| di | Document Information | 2 |
| ec | E-Commerce | 3 |
| ev | Events | 69 |
| ex | Exhibits | 30 |
| fw | Framework/Admin | 28 |
| gn | Grants | 11 |
| gr | Government Relations | 13 |
| jb | Job Bank | 3 |
| lm | List Management | 5 |
| mb | Membership | 40 |
| md | Metadata/Toolkit | 84 |
| mk | Mailing List | 5 |
| mq | Messaging Queue | 7 |
| np | Fundraising (nonprofit) | 18 |
| oe | Inventory - Order Entry and Inventory Management | 48 |
| sf | Sales Force | 11 |
| su | Subscription | 12 |
| tm | Time and Materials | 15 |
| ts | Task Scheduler | 10 |
| wf | Work Flow | 4 |
| ws | Web Service | 5 |
Common Mistakes
Please avoid these common mistakes:
- Using UPPERCASE letters in a column or table name. The names must be all lowercase. Errors occur if you have any mixed-case names, because certain areas of the netFORUM framework assume all lower case.
- Forgetting to put _flag in the column name of a column of data type av_flag. It is very annoying to see a column such as a24_delivered and have to guess if this is a date, a flag, a message, or what? Please just be clear and complete. Also, this data type is NOT NULL.
- Not putting the mandatory _ext at the end of an extender column's name.
- Carelessly allowing mandatory columns to allow NULL values, especially when the table is a child table and has a foreign key column to a parent table and that foreign key column is NULLABLE (e.g. client_xyz_customer_action.a98_cst_key allows NULL, which is fundamentally wrong.) Part of the blame for this is SQL 2000 defaults columns to ALLOW NULL, whereas SQL 2005 defaults to NOT NULL which should minimize this oversight in the future.