The objective of this document is to provide a standard for database objects (such as tables, columns, and indexes) for products using Oracle Utilities Application Framework. This standard is introduced to insure 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 QA processes, field tests and benchmarks.
This section discusses the rules applied to naming database objects and the attributes that are associated with these objects.
Column Data Type and Constraints
The following naming standards must be applied to database objects.
Contents
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. For example, MD stands for meta-data subsystem and all meta data table names start with CI_MD.
Some examples are:
· CI_ADJ_TYPE
· CI_ADJ_TYPE_L
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).
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.
The tables prior to V2.0.0 are prefixed with CI_ or SC_.
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.
Some examples are:
· ADJ_STATUS_FLG
· CAN_RSN_CD
Index names are composed of the following parts:
[X][C/M/T]NNN[P/S]
· X – letter X is used as a leading character of all base index names prior to Version 2.0.0. Now the first character of product owner flag value should be used instead of letter X. For client specific implementation index in Oracle, use CM.
· C/M/T – The second character can be either C or M or T. C is used for control tables (Admin tables). M is for the master tables. T is reserved for the transaction tables.
· NNN – A three-digit number that uniquely identifies the table on which the index is defined.
· P/S/C – P indicates that this index is the primary key index. S is used for indexes other than primary keys. Use C to indicate a client specific implementation index in DB2 implementation.
Some examples are:
· XC001P0
· XT206S1
· XT206C2
· CM206S2
Warning! Do not use index names in the application as the names can change due to unforeseeable reasons.
The base sequence name must be prefixed with the owner flag value of the product.
The base trigger name must be prefixed with the owner flag value of the product.
When implementers add database objects, such as tables, triggers and sequences, the name of the objects should be prefixed by CM. For example, Index names in base product are prefixed by X; the Implementers’ index name must not be prefixed with X.
Contents
Fixed Length/Variable Length Character Columns
User Defined Codes are defined as CHAR type. The length can vary by the business requirements but a minimum of eight characters is recommended. You will find columns defined in less than eight characters but with internationalization in mind new columns should be defined as CHAR(10) or CHAR(12). Also note that when the code is referenced in the application the descriptions are shown to users in most cases.
System assigned random numbers is defined as CHAR type. The length of the column varies to meet the business requirements. Number type key columns are used when a sequential key assignment is allowed or number type is required to interface with external software. For example, Notification Upload Staging ID is a Number type because most EDI software uses a sequential key assignment mechanism. For sequential key assignment implementation, the DBMS sequence generator is used in conjunction with Number Type ID columns.
Date, Time and Timestamp columns are defined physically as DATE in Oracle. In DB2 the DATE, TIME and TIMESTAMP column types, respectively, are used to implement them. Non-null constraints are implemented only for the required columns.
Numeric columns are implemented as NUMBER type in Oracle and DECIMAL type in DB2. The precision of the number should always be defined. The scale of the number might be defined. Non-null constraints are implemented for all number columns.
When a character column is a part of the primary key of a table define the column in CHAR type. For the non-key character columns, the length should be the defining factor. If the column length should be greater than 10, use VARCHAR2 type in Oracle and VARCHAR type in DB2.
The Non-null constraints are implemented for all columns except optional DATE, TIME or TIMESTAMP columns.
The rule to set the database default value is the following:
· When a predefined default value is not available, set the default value of Non-null CHAR or VARCHAR columns to blank except the primary key columns.
· When a predefined default value is not available, set the default value Non-null Number columns to 0 (zero) except the primary key columns.
· No database default values should be assigned to the Non Null Date, Time, and Timestamp columns.
Referential Integrity is enforced by the application. In database, do not define FK constraints. Indexes are created on most of Foreign Key columns to increase performance.
Contents
Owner Flag (OWNER_FLG) columns exist on the system tables that are shared by multiple products. Oracle Utilities Application Framework limits the data modification of the tables that have owner flag to the data owned by the product.
The Version column is used to for optimistic concurrency control in the application code. Add the Version column to all tables that are maintained by a Row Maintenance program irrespective of the language used (COBOL or JAVA).