Re: Setting standards

From: Mark D Powell <mark.powell_at_eds.com>
Date: 7 Sep 2001 06:03:59 -0700
Message-ID: <178d2795.0109070503.2f09dc6@posting.google.com>

Svend Jensen <svend_at_oraclecare.com> wrote in message news:<3B97C095.86A1BB3D_at_oraclecare.com>...
> First there is THE INSTALLATION STANDARD - the OFA
> Optimal Flexible Architecture by Cary Millsap. A white paper.
>
> Then there is the coding std. (there are many around - some god)
> I prefer the std. where everything has a long and a short name and
> all table columns have the shortname prefixed.
> Table DEPTARTMENT shortname D (or DEP),
> column LOCATION is named D_LOCATION (if D is short)
> Nonunique indexes on column is named I_D_LOCATION,
> uniques start with U_, primary keys PK_ foreign keys FK_
> function based FB_ and index org tables IOT_ ...
>
> Then there is the backup std. I prefer archivelog mode with
> hot backup every night after a workday, cold backup sundays
> in the range once a week to once every 3 months. Depending
> on how much work (and time) there is to recover ~ x* update done.
>
> And then there is much more to it.... happy hunting.
>
> /Svend Jensen
>
> Myron Murff wrote:
>
> > Does anyone have any recommendations for developing "standards", for all
> > aspects of operation, in a virgin Oracle environment?

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 base name as the index that is used to support the constraint: table_name_pk

Index names:
1) For the primary constraint key to a table the name will be table_name_pk
2) For a unique constraint 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: A.key = B.key

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 by showing developer intent and supporing ORDERED hint.

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

• Mark D Powell --

Received on Fri Sep 07 2001 - 08:03:59 CDT