Database Naming Convention

Oracle Schema Naming Standards

The following standards will be used in all schemas:

• Schema objects - All non-table schema objects will be prefixed by their type and all index names will begin with idx, and all constraint names will begin with cons.
   
• Referential Integrity conventions - All tables will have full RI, including PK constraints, FK constraints and check constraints. The default for foreign key constraints will be "On Delete Restrict", unless otherwise specified.  This means that no parent record can be deleted if there are corresponding child records.
   
• Primary keys - Oracle Sequences will be used to generate unique row identifiers and all sequence numbers generally will start at one and increment by one.
   
• Check Constraints - Lists of valid values will be used in all cases to restrict column values and validity

Oracle table naming Standards

To simplify development, we will follow these rules that allow the developer to quickly identify each metadata object, with complete descriptive names:

• Table Standards
   
  • All table names will be plural (e.g. users vs. user).
  • Full table names will be used whenever possible.
  • If a table name should exceed 30 characters, reduce the size of the table name in this order:
    • From the left of the table name, remove vowels from each word in the table name except for the first vowel of each word.
    •  If the table name is still greater than 30 characters, use standardized shorthand indicators. Record this standard for consistent use.
       

Oracle column naming Standards

• Column Naming Standards
  • Column names should be spelled out whenever possible.
  • If a column name should exceed 30 characters, reduce the size of the column name in this order:
    • From the left of the column name, remove vowels from each word in the table name except for the first vowel of each word.
    •  If the column name is still greater than 30 characters, use standardized shorthand indicators. Record this standard for consistent use.

Oracle index naming Standards

• Index Standards
  • Index names should follow this standard:

IDX_ttttt_nn

Where IDX = Index
tttt = Table name the index is built on
nn = Numeric value that makes each table index unique.

• If an index name should exceed 30 characters, reduce the size of the index name in this order:
  • From the left of the index name, remove vowels from each word in the table name except for the first vowel of each word.
  •  If the index name is still greater than 30 characters, use standardized shorthand indicators. Record this standard for consistent use.

Oracle constraints naming Standards

• Constraint Standards
  • Primary key constraints will follow this naming convention:
    • PK_nnnnn 
      Where nnnn = The table name that the index is built on.
      
       
    • UK_nnnnn_nnWhere nnnn = The table name that the index is built on.
                      nn =  A number that makes the constraint unique.
      
       
    • FK_pppp_cccc_nn
      Where pppp = The parent table name
                  cccc = The child parent table name
                      nn = A number that makes the constraint unique
      
       

Sample names might include:

• tables names - persons, islands, dsm_iv_codes
  
   
• table column names - first_name, dsm_iv_code_description
  
   
• constraint names - pk_ehd_food_establishment, fk_ehd_food_establishment_01
  
   
• index names - idx_ssd_dsm_01

Oracle application naming Standards

Application Prefixes - All table/index/column/constraint names will use standard prefixes.  Each application area will be identified with a three-character abbreviation, and this abbreviation will be used for the names of all tables, indexes and constraints.  We will not use system-generated constraint or index names.  For example, assume we have these two application areas:

• General cross-area objects = GEN
• Social Services Department = SSD
• Health Services Department = HSD

Object names - To simplify development, we will follow these standards that allow the developer to quickly identify each metadata object, with complete descriptive names:

• The application prefix will be used in all metadata entries, including tables, indexes, constraints and table columns.
   
• The table name will be included in all index, constraint and table column names.
   
• The type of constraint will be specified in all constraint names, using the abbreviations PK, FK and CHECK

References:

-http://www.dba-oracle.com/standards_schema_object_names.htm
-http://www.toadworld.com/Portals/0/stevenf/Naming%20Conventions%20and%20Coding%20Standards.pdf