ITS - Information Systems

Oracle Naming Conventions

1. Applications
   1. Applications will be identified by a name and a 2 or 3-letter code (APP in what follows).
   2. If a database contains several distinct applications, the name of all objects, which are unambiguously part of the application, will be prefixed by the application code APP_ .
2. User-defined Types
   1. User-defined types will be named [APP_]Name_t.
3. Partitions
   1. Partitions will be named Name_Pn, where Name is the name of the partitioned table or index and n is a number to uniquely identify the partition. Where a table is partitioned by year, n is the year.
4. Tables and table columns
   1. Tables will be named [APP_]Name, with Name at most 24 characters long. References to TableName below implicitly include the application code prefix if it is used
   2. Table names will always be plural.
   3. Table names should not contain spaces, should be split_up_with_underscores. Only alphanumeric characters and underscores ‘_’ will be used (no #, no $) in 'base names'.
   4. Column names names will be singular or plural depending on the meaning for a single row. Examples : EmpName, Salary but Children or HoursWorked
   5. Column names will be at most 30 characters long
   6. Do not prefix column names.
   7. It is highly recommended, though not required, that each table have a primary key defined
   8. It is highly recommended, though not required, that primary key columns be suffixed with _id
   9. The first column(s) of a table will be required to be the primary key column(s), if a primary key is defined. This will allow better identification of the primary keys for a table.
   10. The foreign key column(s) of a table need to immediately follow the primary key columns of the table, if a foreign key(s) is defined. This will only be applicable when the table does not exist in production database.
5. Indexes
   1. NONUNIQUE Indexes will be the table name suffixed with _nn such as table_name_n1
   2. UNIQUE Indexes will be the table name suffixed with _un such as table_name_u1
   3. BITMAPPED Indexes will be the table name suffixed with _bn such as table_name_b1
   4. FUNCTION based Indexes will be the table name suffixed with _fn such as table_name_f1
   5. See Also: 6. Integrity Constraints
6. Integrity Constraints
   1. All integrity constraints except NOT NULL constraints must be explicitly named
   2. Primary Keys
   3. Primary Keys will be the table name suffixed with _pk such as table_name_pk
   4. Foreign Keys
   5. Foreign Keys will be the table name suffixed with _fkn where n is a number used to uniquely identify the foreign key constraint in case of multiple foreign keys. Example: table_name would have a foreign key table_name_fk1.
   6. All foreign keys are required to have an index built on them. The index should follow the standard naming conventions listed under indexes.
   7. Unique Keys
   8. Unique Keys will be the table name suffixed with _uk.
   9. Other Constraints
   10. CHECK constraints will be named TableName_ColumnName_ck12:13 PM 1/18/2005
   11. Naming NOT NULL constraints is not required or recommended.
7. Views
   1. Views will be named [APP_]Name, where Name follows the same naming rules as tables.
8. Synonyms
   1. Synonyms will be exclusively used to hide the fact that an object belongs to another schema. They will therefore take the same name as the object they are a synonym for.
9. Sequences
   1. Sequences which are directly used as such to generate a primary key value will be named TableName_ID_S
   2. Sequences which are used to partially generate a value or for which there is a type conversion, will be named TableName_ColumnName#[_p]_S where p is an optional sequence number to uniquely identify several sequences which would be used to generate a single column.
10. Triggers
    1. Triggers will be named TableName_Tn<E><W> where E is the event which fires the trigger (U - Update, I - Insert, or D - Delete). W is When the trigger is fired (B - Before, or A - After). 'n' is the number of the trigger (1,2,3,...). The trigger that fires when a table is updated, before the update occurs would be named table_name_T1_UB
11. Packages, Functions, and Procedures
    1. Packages will be named [APP_][Module_]Name_pkg
    2. Stand-alone functions will be named [APP_][Module_]Name
    3. Functions in packages will be named Name. The optional application and module indication will be indicated at the package level only.
    4. Stand-alone procedures will be named [APP_][Module_]Name
    5. Procedures in packages will be named Name. The optional application and module indication will be indicated at the package level only.
    6. Recommendation: Packages should be used whenever possible.
12. Database Links
    1. Database links will bear the name of the application which ‘owns’ the tables to be accessed. Connection will be done using an account whose name is the name of the current application.

Reference:

• "Oracle Naming Standards." 1999. Oriole Corporation. www.oriolecorp.com. 27 August 2002 www.oriolecorp.com/naming.html

The naming conventions above provide a number of benefits. Many of these are more to do with human factors than any system limitations. There is no universal truth in the matter of naming standards. The only thing that matters is to define a consistent set of standards and then, when they have been accepted, to stubbornly stick to them. As long as they have not been implemented, the above rules can be amended at will.

Other notes:

• DATATYPES: Always use VARCHAR2 when defining character fields unless there is justification for using CHAR. Refer to the following points found in Oracle8i Application Developer's Guide - Fundamentals:
  
  When deciding which datatype to use for a column that will store alphanumeric data in a table, consider the following points of distinction:
  
  
  1. Space Usage
     To store data more efficiently, use the VARCHAR2 datatype. The CHAR datatype blank-pads and stores trailing blanks up to a fixed column length for all column values, while the VARCHAR2 datatype does not blank-pad or store trailing blanks for column values.
     
     
  2. Comparison Semantics
     Use the CHAR datatype when you require ANSI compatibility in comparison semantics (when trailing blanks are not important in string comparisons). Use the VARCHAR2 when trailing blanks are important in string comparisons.
     
     
  3. Future Compatibility
     The CHAR and VARCHAR2 datatypes are and will always be fully supported. At this time, the VARCHAR datatype automatically corresponds to the VARCHAR2 datatype and is reserved for future use.
  
  
• ABBREVIATIONS: When creating database indexes, foreign keys and other objects that are based on the original table name, there are times that the name becomes too long. To deal with this, our standard is to remove the vowels from the last word in the object name. For example for a table named POS_SUB_PROGRAM_REQUIREMENT adding on an _FK makes the name too long. As a result the name of the FK object is made POS_SUB_PROGRAM_RQRMNT_FK2. The vowels in the word requirement were removed.

Change Log: