Re: naming standards

From: Mark D Powell <markp7832_at_my-deja.com>
Date: 2000/05/16
Message-ID: <8fs73j$rj1$1@nnrp1.deja.com>#1/1

In article <04ca1ef0.e7ff2314_at_usw-ex0103-024.remarq.com>,   mcomer <mcomer2NOmcSPAM_at_hotmail.com.invalid> wrote:
> Looking for information about database, table, and column
> standards - would like to implement - don't want to reinvent the
> wheel.
>
> Thanks
>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion
 Network *
> The fastest and easiest way to search and participate in Usenet -
 Free!
>

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 converntion 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

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

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

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue May 16 2000 - 00:00:00 CDT