Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Suggestions for naming conventions
In article <3ae49431.3379158_at_enews.newsguy.com>, <knighthing_at_hotmail.com>
writes:
>I have been asked to come up with naming convetions for an Oracle
>database.
>
>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:
11/10/2000
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
Constraints:
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 underscoreidxN 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 isunnecessary as columns can/will be identified in the SQL with table name lables
SQL:
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.
PL/SQL:
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