Database schema object naming considerations
As you customize your e-commerce application, you may create new database tables. When creating these tables, IBM recommends that you follow a set of conventions, so that your tables are created in a manner consistent with the WebSphere Commerce tables.
Subsequent sections provide guidance for the naming of database schema objects.
Naming conventions for tables and views
The following list provides guidance for the naming of new tables and views:
- In order to avoid name collision (duplicate names) with WebSphere Commerce tables and views in future releases, the first character in the table or view name should be X. For example, XMYTABLE.
- The table or view name should be no more than 10 characters in length. If the desired name exceeds this limit, shorten the length by removing vowels from the end of the name, until only 10 characters remain.
- The table or view name should not contain any special characters, such as "_", "+", "$", "%", or blank spaces.
- Do not use database reserved words as a table or view name.
- View names should end with VW.
- The table and view names should be singular nouns.
Naming conventions for columns
In general, when you create new tables that follow the preceding conventions for table names, you can implement your own naming convention for columns within those tables. This assumes that you always use fully-qualified column names in SQL statements. If, however, you want to perform joins with existing WebSphere Commerce tables and you do not want to use fully-qualified column names, then follow the column naming conventions described in this section.
The following list provides guidance for the naming of columns in new tables:
- In order to avoid name collision (duplicate names) with columns in WebSphere Commerce tables in future releases, the first character in the column name should be X. For example, XMYCOLUMN.
- The column name should be no more than 18 characters in length. If the desired name exceeds this limit, shorten the length by removing vowels from the end of the name, until there are only 18 characters.
- Columns names (other than foreign keys) should not contain any special characters, such as "_", "+", "$", "%", or blank spaces.
- Do not use database reserved words as a column name.
- Combined words may be used as column names using the active voice combination. For example, COMBINERESULT.
- The generated primary key columns should be named as table_id. For example, the primary key for the USERS table is USERS_ID.
- The generated foreign key column names should not be changed.
- If reserving any columns for future customization, they should be named fieldx where x is a numeric digit starting from 1.
Naming conventions for indexes
The following list provides guidance for the naming of indexes in new tables:
- The index name should be no more than 18 characters in length.
- The index name should not contain blank spaces.
- The index name should not contain any database reserved words.
- A non-unique index should be named as I_tablex where table is the name of the table and x is a number, beginning at 1. For example, a non-unique index for the USERS table is I_USERS1.
- A unique index should be named as UI_tablex where table is the name of the table and x is a number, beginning at 1. For example, a unique index for the USERS table is UI_USERS1.
- The total size of the index should be no larger than 254 bytes.
- The index name must be unique across the whole database schema.
Naming conventions for primary keys
The following list provides guidance for the naming of primary keys for new tables:
- The primary key name should be no more than 18 characters in length.
- The primary key name should not contain blank spaces.
- The primary key name should not contain any database reserved words.
- The primary key should be named as P_table where table is the name of the table. For example, the primary key for the USERS table is P_USERS.
- The primary key name must be unique across the whole database schema.
Naming conventions for foreign keys
The following list provides guidance for the naming of foreign keys for new tables:
- The foreign key name should be no more than 18 characters in length.
- The foreign key name should not contain blank spaces.
- The foreign key name should not contain any database reserved words.
- The foreign key should be named as F_tablex where table is the name of the table and x is a number that starts from 1. For example, the foreign key for the USERS table is F_USERS1.
- The foreign key name must be unique across the whole database schema.
Naming conventions for database triggers
The following list provides guidance for the naming of database triggers:
- The database trigger name should be no more than 18 characters in length.
- The database trigger name should not contain blank spaces.
- The database trigger name should not contain any database reserved words.
- The database trigger should be named as T_tablex where table is the name of the table and x is a number that starts from 1. For example, the database trigger name for the USERS table is T_USERS1.
- The database trigger name must be unique across the whole database schema.
Related reference