Appendix - Database Naming Conventions

This section provides a standard for database objects (such as tables, columns, and indexes) for products using Oracle Utilities Application Framework. This standard is introduced to ensure clean database design, to promote communications, and to reduce errors leading to smooth integration and upgrade processes. Just as Oracle Utilities Application Framework goes thorough innovation in every release of the software, it is also inevitable that the product will take advantage of various database vendors' new features in each release. The recommendations in the database installation section include only the ones that have been proved by vigorous quality assurance processes, field tests and benchmarks.

The following naming standards must be applied to database objects.

Tables

Table names are prefixed with the owner flag value of the product. For customer modification CM must prefix the table name. The length of the table names must be less than or equal to 30 characters. A language table should be named by suffixing _L to the main table. The key table name should be named by suffixing _K to the main table.

It is recommended to start a table name with the 2-3 letter acronym of the subsystem name that the table belongs to.

Some examples are:

• CM_MYTYPE

• CM_MYTYPE_L

Note: A language table stores language sensitive columns such as a description of a code. The primary key of a language table consists of the primary key of the code table plus language code (LANGAGUE_CD).

Note: A key table accompanies a table with a surrogate key column. A key value is stored with the environment id that the key value resides in the key table.

Note: Tables prior to V2.0.0 are prefixed with CI_ or SC_.

Columns

The length of a column name must be less than or equal to 30 characters. The following conventions apply when you define special types of columns in the database.

• Use the suffix _FLG to define a lookup table field. Flag columns must be CHAR(4). Choose lookup field names carefully as these column names are defined in the lookup table (CI_LOOKUP_FLD) and must be prefixed by the product owner flag value.

• Use the suffix _CD to define user-defined codes. User-defined codes are primarily found as the key column of the admin tables.

• Use the suffix _ID to define system assigned key columns.

• Use the suffix _SW to define Boolean columns. The valid values of the switches are 'Y' or 'N'. The switch columns must be CHAR(1)

• Use the suffix _DT to define Date columns.

• Use the suffix _DTTM to define Date Time columns.

• Use the suffix _TM to define Time columns.

• Use the suffix _AREA to define XML Type and CLOB columns

Some examples:

• ADJ_STATUS_FLG

• CAN_RSN_CD

The following database column types are supported:

• CHAR

• CLOB

• DATE (includes Date and time)

• NUMBER

• VARCHAR2

• XMLTYPE

Indexes

Index names are composed of the following components:

Index components include the following:

Component	Description and Comments
<prefix>	All indexes must be owned appropriately. For client specific implementation indexes, use CM as the prefix to avoid conflicts with base product indexes.
<datatype>	For base product use only. Indicates type of data stored in table. Valid values: • C indicates the table is an Administration/Control Table • M indicates the table is a master table. • T indicates the table is a transaction table.
<number>	Unique three-digit number that makes the index name unique for a table. All indexes for the same table should share the same number.
<indextype>	Type of index. Valid values: • P indicates that this index is the primary key index • S indicates that this index is a non-primary index
<suffix>	Suffix to support multiple indexes on a table using the following rules: • 0 (zero) as a suffix is reserved for the primary index • 1 - 9 is reserved for each non-primary index on a table.

Examples:

• XC001P0

• XT206S1

• CM206S2