Tracy,
A scheme that I have used for years that, IMHO, works well to help everyone
automatically know "what's what" centers on table aliases.
We have only about 150 tables in our production db, so our table-naming
standard is pretty simple - name it for what it is. Information about our
clients is contained in the Clients table. Information about the Sites from
which we download documents is contained in the Sites table...you get the
idea. Intersect tables are named "sensibly" - information about which Sites
a Client will extract documents from is held in Client_Sites. Site
categorization info. is held in Site_Types, while info. about which types of
Sites are of interest to any particular Client is held in Client_Site_Types.
Pretty straightforward.
The table alias concept comes in when naming everything else - constraints,
indexes, triggers, packages, etc. A table alias is derived by taking the
first four letters of the table name plus the first letter of any additional
words in the table name. The above-mentioned tables would have aliases as
follows:
Clients = Clie
Sites = Site
Client_Sites = ClieS
Site_Types = SiteT
Client_Site_Types = ClieST
This method of deriving aliases results in surprisingly few duplication
problems, which can usually be solved quite easily. For instance, we have
Mailing_List_EMails and Mailing_List_Exceptions - the former's alias is
MailLE, while the latter's is MailLX; pretty easy to derive and remember.
BTW, credit for using this form of table alias goes to David Wendelken -
Author, Oracle Designer Guru, Business Rule Engine Magician, and long-time
contributor to the advancement of quality in the Oracle Development Tools
community - from whom I first heard it described.
All objects derived from the table use its alias following this pattern:
<TableAlias>_<Type>_<ColName>
where Type would be PK (Primary Key), UK (Unique Key), Ndx (Index), CK
(ChecK Constraint). We designate Unique Indexes as NdxU and function-based
indexes as NdxF. If there are multiple columns in the constraint or index,
we abbreviate them or just don't include them if there are too many.
Examples using Clients, Sites, and Client_Sites are:
PK for Clients = Clie_PK_Client_ID
UK for Sites = Site_UK_Site_Name
Index for Sites.Site_Type = Site_Ndx_Site_Type
Function-based index on Lower(Site_Name)
= Site_NdxF_Site_Name
Check Constraint that either the Domain OR the EMail must be present
= Clie_CK_Dom_XOR_EM
An advantage to this is that all table and index segments sort together in
User_Segments, etc.
Foreign Keys are named <FKTableAlias>_FK_<PKTableAlias>_<FKColName> so that
the FK from Sites to Site_Types would be Site_FK_SiteT_STID (STID being an
abbreviation of Site_Type_ID).
Triggers are named <TableAlias>_Trg_<B or A>IUD<R or S>. We allow a maximum
of 4 triggers on a table - Before Statement, Before Row, After Statement,
After Row. Each one contains minimal logic for Insert, Update, and Delete
actions. The triggers for Client_Site_Types are:
ClieST_Trg_BIUDS (Before Insert, Update, or Delete Statement)
ClieST_Trg_BIUDR
ClieST_Trg_AIUDR
ClieST_Trg_AIUDS
The "minimal logic" typically calls packaged procedures that do the bulk of
the triggered processing. There's a PL/SQL package for each table that has
table-specific "stuff" - public cursors, functions, and procedures, as well
as public PL/SQL records and tables, to promote reusable code. Packages are
named pkg<TableAlias>, so pkgClieST is the table-specific package for
Client_Site_Types.
Procedures are prefixed with p, functions with f, and cursors with c. The
packaged procedure for handling the Before Statement Trigger logic that
would be called by ClieST_Trg_BIUDS is pkgClieST.pTrgBIUDS. In fact,
there's a pTrgBIUDS, as well as a pTrgBIUDR and pTrgAIUDR and pTrgAIUDS,
procedure in every table's package - qualified by their package names
they're unique.
We've also got standards for PL/SQL coding, but that's probably outside the
DBA scope that you seem to be interested in.
With our naming standard, everyone knows exactly where to find specific
pieces of code - commonly named subprograms in packages named using an
easy-to-remember table alias. Also, error messages involving constraint
violations immediately tell you which table, even which column, is involved.
I could go on and on...Hope this gives you some helpful ideas. Table
aliases are the key!
Jack
--------------------------------
Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068
-----Original Message-----
Rahmlow
Sent: Friday, July 13, 2001 10:41 AM
To: Multiple recipients of list ORACLE-L
Were looking to develop naming standards within our organization and I am
wondering what others use. Is there a formal process similiar to ofa? Do
shops typically use underscores or case? (policy_number / PolicyNumber)
What
about abbreviating? Enforcement processes ? Other considerations?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack C. Applewhite
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
