Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Suggestions for naming conventions

Re: Suggestions for naming conventions

From: <>
Date: 24 Apr 2001 13:59:39 GMT
Message-ID: <9c40sb$1nc$>

In article <>, <> writes:
>I have been asked to come up with naming convetions for an Oracle
>Unfortunately, I'm a SQL Server DBA who's just been thrown into the
>Oracle beast last week.
>Any suggestions would be greatly appreciated.

There are probably as many standards as they are IT shops but here are a few I like:

Table names:
1) short, sigular, and meaningful
2) limit the name to 23 characters to allow use of replication at some point in time and to support index naming convention 3) once a word is abbreviated in one table name, abbreviate it in all names, i.e., if it is hist for history in one table make it hist everywhere

Primary and Unique constraints will be explicitly named and given the same name as the index that is used to support the constraint

Index names:

1) For the primary key to a table the name will be table_name_pk
2) For a unique index the name will be in the form table_name_uk   
3) All other index names will consist of the table_name with an underscore
idxN appended as in cust_mast_idx1, cust_mast_idx2 etc...

Note - Conventions that attempt to use the column name in the index often break down when the same column appears in multiple indexes on the same table.

Column names:

1) short and meaningful
2) The abbreviation rule for tables applies
3) Do not prefix the column names with a table name abbreviation.  This is
unnecessary as columns can/will be identified in the SQL with table name lables

1) All lowercase, forget capitalizing the SQL keywords 2) Label every table_name in the from clause of a join and prefix every column name occurrance in the SQL with the label 3) List tables in the from clause in desired join order 4) Refer to column values in the where clause in desired join order

Note items 3 and 4 have to do with assisting tuning efforts with the CBO.

Short meaningful names and the abbreviation rule apply 1) scalar variables start with v_
2) variables received as parameters on a procedure or function call start with p_
3) collections are named
  a - tables as t_
  b - records and %rowtype as r_
4) cursors are referenced as c_
5) scalar and manually defined record variables definded to hold or feed a column should have the same name as the column but preceeded by the appropriate prefix, v_column_name

Received on Tue Apr 24 2001 - 08:59:39 CDT

Original text of this message