Object Naming Standards

Copyright Oriole Corporation, 1999

Introduction

There is no universal truth in the matter of naming standards. The only thing which 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 following rules can be amended at will.

  1. Applications
    2. Rule ON1 Applications will be identified by a name (APPNAME in what follows) and a 3-letter code (APP in what follows).
    Rule ON2 If the database harbors several distinct applications, the name of all objects which are unambiguously part of the application will be prefixed by the application code APP.
  2. Roles
    3. Rule ON3 Roles will be named [APP_][Module_]Priv
    where APP is the 3-letter application code and Priv indicates a level of privilege, such as ADM, USR, SEL, etc. The optional Module may indicate the scope of the role.
    Rule ON4 Applicative roles will not refer to any system privilege but exclusively grants on objects.
  3. User defined types
    4. Rule ON5 User-defined types will be named [APP_]Name_T
  4. Clusters
    5. Rule ON6 Clusters will not be used
  5. Partitions
    6. Rule ON7 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.
  6. Tables and table columns
    7. Rule ON8 Tables will be named [APP_]Name, with Name at most 10 characters long.
    References to TableName below implicitly include the application code prefix if it is used
    Rule ON9 Column names will be at most 10 characters long
    Rule ON10 Only alphanumeric characters will be used (no _, no #, no $) in 'base names'.
    For legibility (although Oracle is case-insensitive) uppercase letters will be used to indicate the start of words when writing statements.

    Example : EmpNo
    Rule ON11 Table names will always be plural

    Example : Table Employees

    Rule ON12 Column names will be singular or plural depending on the meaning for a single row.

    Examples : EmpName, Salary but Children or HoursWorked

    Rule ON13 Tables which implement a (0,n) or (1,n) relationship will be named as the concatenation, separated by _, of the four or three first characters of the names of the two or three tables they link.

    Exception to rule ON13 

    • Relationship tables which represent some kind of entity for end-users will bear a name similar to the names of true entities.
    Rule ON14 Column names are independent from table names. Foreign key columns bear the same name as the primary key columns they refer to

    Exceptions to rule ON14 

    • Columns of self-referencing tables (EmpNo <- Mgr)
    • Columns which are semantically different from the columns they refer to, i.e. for which the meaning of the data is quite particular (UpdatedBy which references UserName but always means the identifier of the last user to have updated the row).
  7. Indexes
    1. Indexes implementing an integrity constraint

      2. See integrity constraint naming standards

    2. Alternate indexes
      3. Rule ON15 Single-column indexes will be named TableName_IDX_ColumnName
      Rule ON16 Concatenated indexes will be named TableName_IDXn[_p]
      where n is the number of columns in the index and p is an optional sequence number to uniquely identify several indexes with the same number of columns
  8. Integrity constraints
    9. Rule ON17 All integrity constraints except NOT NULL constraints must be explicitly named
    1. Primary keys

      2. Note : Wherever possible, primary keys should be number columns

      Rule ON18 Single-column primary key constraints will be named TableName_PK_ColumnName
      Rule ON19 Multiple-column primary key constraints will be named TableName_PKn
      where n is the number of columns in the primary key.
    2. Unique columns
      3. Rule ON20 Single-column unique constraints will be named TableName_U_ColumnName
      Rule ON21 Multiple-column unique constraints will be named TableName_Un[_p]
      where n is the number of columns in the index and p is an optional sequence number to uniquely identify several unique constraints with the same number of columns.
    3. Foreign keys
      4. Rule ON22 Single-column foreign key constraints will be named TableName_FK_ColumnName
      Rule ON23 Multiple-column foreign key constraints will be named TableName_FKn[_p]
      where n is the number of columns in the index and p is an optional sequence number to uniquely identify several foreign keys with the same number of columns
    4. Other constraints
      5. Rule ON24 CHECK constraints will be named TableName_CK_ColumnName
      Rule ON25 If they are named (not recommended) NOT NULL constraints will be named TableName_NN_ColumnName
  9. Views
    10. Rule ON26 Views will be named [APP_]V_Name, where Name follows the naming rules for tables.
  10. Synonyms
    11. Rule ON27 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.
  11. Sequences
    12. Rule ON28 Sequences which are directly used as such to generate a primary key value will be named TableName_SEQ_ColumnName
    Rule ON29 Sequences which are used to partially generate a value or for which there is a type conversion, will be named TableName_SEQ_ColumnName#[_p]
    where p is an optional sequence number to uniquely identify several sequences which would be used to generate a single column.
  12. Triggers
    13. Rule ON30 Triggers will be named TableName_TRG_FUNC[_[UID_]<W><S>]
    where FUNC is a code which refers to the function performed by the triggers, such as AUD for an auditing trigger. The following additional elements can be added : an indication of events which fire the trigger (U for Update, I for Insert, D for Delete); this is unnecessary if the trigger is fired by all DML statements. One can also add a two-letter code in which W indicates when the trigger is fired and is B or A depending on the trigger's being fired Before or After the change takes place, and S indicates the scope and is R or S if the trigger is fired for each Row or once per Statement. This indication is unnecessary if all triggers associated with a function works the same way and if this properly documented.
  13. Packages, functions and procedures
    14. Rule ON31 Packages will be named [APP_]PKG_[Module_]Name
    Recommendation
    RN1    		 Stand-alone functions and procedures will be avoided
    Rule ON32 Stand-alone functions will be named [APP_]F_[Module_]Name
    Rule ON33 Functions in packages will be named F_Name
    The optional application and module indication will be indicated at the package level only.
    Rule ON34 Stand-alone procedures will be named [APP_]P_[Module_]Name
    Rule ON35 Procedures in packages will be named P_Name
    The optional application and module indication will be indicated at the package level only.
  14. Database links
    15. Rule ON36 Database links will bear the name of the application which 'owns' the tables we want to access. Connection will be done using an account the name of which is the name of the current application.
  15. Snapshots
    16. Rule ON37 Snapshots will be named TableName_SNP[_p]
    where p is an optional sequence number to uniquely identify several snapshots based on the same table.
previous pageDatabase creation standards Back to Standards Intro. next page