Oracle Naming Conventions

When designing a database it's a good idea to follow some sort of naming convention. This will involve a little thought in the early design stages but will save significant time when maintaining the finished system.

It's less important which exact conventions you choose to follow - but this page has a few suggestions.

The benefits of using a naming convention are more to do with human factors than any system limitations - but this does not make them any less important.

Table Names

Table names are plural, field name is singular

Table names should not contain spaces, should be split_up_with_underscores. Limit the table name to 23 characters.

It is a bad idea to use a prefix or suffix to identify tables. Suppose, your naming convention is to have the '_TAB' suffix for all tables. According to that naming convention, the department table would be called DEPART_TAB. As time goes by, your application gets a second login, perhaps for auditing, or for security reasons. To avoid code changes, we use synonyms or views that point at the original tables. Now we would have to create a synonym that is called DEPART_TAB. Confusing at best.

Field Names

Fields should be unique within the database schema.

The convention is to prefix the fieldname with a 2 or 3 character contraction of the table name e.g.

patient_options would have a field called po_patient_option

patient_relatives would have a field called pr_relative_name

absences would have a field called ab_start_date

In a large schema you will often find two tables having similar names which could result in the same prefix. You can avoid this by thinking carefully about the name you give each table - and documenting the prefixes to be used. This is a pain initially but the benefits are well worth it.

Primary Key Fields - indicate by appending _pk

so

PATIENTS would have a primary key called pa_patient_id_pk

REGIONS would have a primary key called re_region_id_pk

And so on...the name of the primary key field being a singular version of the table name. Other tables containing this as a foreign key would omit the _PK

so
CLINIC_ATTENDANCE might then have a foreign key called ca_patient_id
or alternatively: ca_patient_id_fk

Tables with Compound PK's, use _ck in place of _pk

Notice that where several tables use the same PK as part of a compound foreign key then the only unique part of the FK fieldname will be the table prefix.

Index names

Name the Primary Key index as idx_<TableName>_pk

so
PATIENTS would have a primary key index called idx_patients_pk

Name a Unique Index as idx_<TableName>_uk

so
PATIENTS would have a unique index called idx_patients_uk

Where more indexes are added to the same table, simply append a numeric:

idx_<TableName>_##

Where ## is a simple number

so
PATIENTS would have additional indexes called idx_patients_01, idx_patients_02,...

Note - Conventions that attempt to use the column name as part of the index name become unmanageable as soon as you have multiple columns appearing in multiple indexes.

Constraints

Primary and Unique constraints will be explicitly named.

Name the Primary Key Constraint as pk_<TableName>

so
PATIENTS would have a primary key index called pk_patients

Name a Foreign Key Constraint as fk_<TableName>

so
PATIENTS would have a Foreign Key constraint called fk_patients

Note - in general each constraint should have a similar name to the index used to support the constraint.

View Names

View names are plural, field name is singular

View names should not contain spaces, should be split_up_with_underscores and prefixed with 'vw_' This ensures the view name remains readable even where case is not preserved.

vw_your_view

vw_another_example

Other Fields

Without getting carried away, you can also apply a suffix to non key fields where this is helpful in describing the type of data being stored.
e.g.
A number field used to store boolean (Yes/No) values can be named as: <fieldname>_yn

In lookup tables an easy way to identify the main text field is to name it as a singular version of the tablename
e.g.

asset_types.at_asset_type_id_pk   (Primary Key)
asset_types.at_asset_type         (Text field)
asset_types.at_network_yn         (boolean) 

SQL

Type all SQL statements in lowercase, being consistent with capitalisation improves the caching of SQL statements. A common variant is to put only SQL keywords in capitals.

SELECT
   em_employee_id_pk,
   em_employee,
   ab_start_date
FROM
   employees em,
   absences ab
WHERE
   absences.ab_employee_id=employees.em_employee_id_pk;

You already have a unique prefix worked out for every table, so use the same thing when an ALIAS is required - this makes the SQL much easier to read.

Always list tables in the FROM / WHERE clause in desired join order - even with CBO you are giving the Query Optimiser less work to do.

PL/SQL

Prefix scalar variable names with v_
Prefix global variables (including host or bind variables) with g_
Prefix constants with c_
Prefix procedure or function call parameters (including sql*plus substitution parameters) with p_

Prefix record collections with r_ (alternatively suffix with _record)
Prefix %rowtype% collections with rt_ (alternatively suffix with _record_type)

Prefix pl/sql tables with t_ (alternatively suffix with _table)
Prefix table types with tt_ (alternatively suffix with _table_type)

Suffix cursors with _cursor
Prefix exceptions with e_

If a pl/sql variable is identical to the name of a column in the table Oracle will interpret the name as a column name.

Packages
Prefix package names with PKG_

Write one package for each table - named PKG_TABLENAME, put all other code that logically belongs to the schema, but not to any particular table in a single Schema package PKG_SCHEMANAME.
If, as is likely, more complex grants are required for different groups of users then create an additional package for each workgroup - these should contain no code just wrappers that call procedures/functions in the other packages.
This gives a level of separation between the basic code and the user security/grants and makes it easier to change one without breaking the other.

A pl/sql function name like PAYROLL.TAX_RATE the word PAYROLL could refer to either a schema or a package name.

Edit Replace

If you apply a naming convention and then decide to rename something it may be possible to use Edit-Replace to update the associated code. But consider these two fieldnames:

area_codes.ac_code 
region_area_codes.rac_code

The columns may be unique but one is a substring of the other!

Documentation

Lastly - write and maintain a data dictionary for all data elements - rather than just dumping the Oracle data dictionary into a text document - you should be defining the business meaning of each data item.

Summary

RDBMS naming conventions can become the subject of endless debate - here are a few last things to consider:

Does your naming convention make names longer of shorter?

PURCHASE_ORDER_DATE versus PO_DATE

Will you have novice users writing SQL against the database?
If so will they understand the meaning of things like PO_DATE

Is the naming convention documented somewhere that everyone can find?

Alternative advice on this topic:

Oracle Naming convention
Reddick Naming convention (hungarian notation)
SQL Server Naming conventions
Also this book includes a few naming conventions for Oracle.