Oracle DBA’s must pay careful attention to the structure and naming conventions and naming standards within the database. All applications will reside within the same schema owner and naming conventions will be used to identify table/index components:
Databases & SIDs
SIDs and databases should be named: AAAT[n] where
AAA is a meaningful three to six character abbreviation for the application using this database.
T is a meaningful indication of the database type, environment, and use of the database.
Examples are ‘D’ for development, ‘Q’ for Quality Assurance, ‘S’ for Stage (Pre-production), ‘P’ for production, ‘R’ for reporting, ‘U’ for User Testing, and ‘T’ for training.
n is a one numeric indicator for the database instance required for Oracle RAC Node Number.(optional for Non-RAC, single, stand-alone databases)
The SID and database names can be a maximum of 8 characters.
SID names have to be unique across the network.
The corresponding database name must always match the SID name.
Database names are always uppercase.
“Sterling Ingetrator Application” – EDIP, EDID, EDIQ databases.
“ipage Application” – ORP, ORDEV, ORQA, ORSTAGE databases.
“EBS Oracle Financials Application” – GOLD (Production), DEV, FINTEST (User Acceptance Testing) database instances.
Note: The ipage and EBS Oracle Financials databases pre-date this documentation and therefore, do not comply with these new convention 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
Entities & Tables
All entity names should be singular and may have spaces. These are replaced with ‘_’ by Oracle Designer during table creation:
APPLICATION FUNCTION ROLE
All table names should be plural. Full table names will be used whenever possible. If the table name contains serveral words, only the last one should be plural:
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.
All entities and tables should be defined a unique alias that is used as part of the foreign key definitions. The alias should be an abbreviation of the name such that:
APPLICATIONS = APPL (4)
APPLICATION_FUNCTIONS = APFU (2:2)
APPLICATON_FUNCTION_ROLES = APFR (2:1:1)
APPLICATION_FUNCTION_ROLE_BANANAS = AFRB (1:1:1:1)
APPLICATION_FUNCTION_ROLE_BANANA_APPLES = (Do what you like!)
The bracketed numbers indicate the number of letters used from each word in the name.
Oracle has a 30 character restriction on object names, so table aliases are useful to reduce the length of object names.
Attributes & Columns
Attributes and columns should not be prefixed with a table alias. For the most part this is unnecessary and often very messy.
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.
Keys & Their Columns
Primary Keys are named after the table or its alias with the suffix of ‘_PK’:
Table : APPLICATIONS
Primary Key: APPLICATIONS_PK
Unique Keys are named after the table or its alias with the suffix of ‘_UK’. If more than one unique key is present you may need to add further information to make the key name unique:
Table : APPLICATIONS
Unique Key : APPLICATIONS_UK
Foreign Keys are usually named using the two table aliases, from and to, with the suffix of ‘_FK’:
Relationship: APPLICATION_FUNCTIONS -> APPLCIATIONS
Foreign Key : APFU_APPL_FK
The foreign key column on the dependant table may be named using the full table name or the alias, along with the column name such that:
APPLICATION_FUNCTIONS.APPLICATION_ID -> APPLICATIONS.ID
APPLICATION_FUNCTIONS.APPL_ID -> APPLICATIONS.ID
I prefer the full table name, but the the 30 character limit forces the use of the alias at times.
Indexes are created implicitly to support PKs and UKs. These indexes have the same name as the constraint.
As a rule of thumb, all foreign key columns should be indexed. Indexes to support foreign keys should be named using the foreign key name with the suffix ‘_I’:
Foreign Key : APFU_APPL_FK
Suporting Index: APFU_APPL_FK_I
Other indexes should be created with meaningful names, usually incorporating the table alias and the column name(s) where possible, along with the suffix ‘_I’.
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.
Primary key constraints will follow this naming convention: PK_nnnnn
Where nnnn = The table name that the index is built on.
nnnn = The table name that the index is built on.
nn = A number that makes the constraint unique.
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
Trigger names should be made up of the table name, an acronym representing the triggering action and the suffix “_TRG”:
Table : APPLICATIONS
Action: BEFORE INSERT STATEMENT-LEVEL
Name : APPLICATION_BIS_TRG
Action: AFTER INSERT AND UPDATE ROW-LEVEL
Name : APPLICATION_AIUR_TRG
A role is a set of privileges that can be granted to users or to other roles.
Roles should be named: name _rol where
name is a descriptive name
rol is a constant
Other database objects have a suffix that identifies their object type:
Check Constraints : _CHK
Sequences : _SEQ
Views : _V
Materialized Views: _MV
Types : _T
Directories : _DIR
External Tables : _EXT
PL/SQL Packages : _API
PL/SQL Procedures : _PRC
PL/SQL Functions : _FUN
PL/SQL variables are prefixed with a single letter, if possible, to indiate their type or usage.
Package Global Variables: g_variable_name
Local Variables : l_variable_name
Types : t_type_name
Cursors : c_cursor_name
Exceptions : e_exception_name
Input Parameters : i_parameter_name
Outut Parameters : o_parameter_name
In/Out Parameters : io_parameter_name
Sometimes I flip back to using a generic “p_” prefix for parameters, regardless of their IN/OUT usage. Old habits dies hard.
File extensions include:
.pks – Package specification.
.pkb – Package body.
.sql – Everything else.
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
Oracle table codes naming Standards
Whenever possible, “codes” will not be used, and the actual data values will be stored in the tables. For example, we will use full-values for all standardized data columns:
ehd_establishment_type in (