| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Naming Standards - Thoughts Needed
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
-----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: japplewhite_at_inetprofit.com 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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Fri Jul 13 2001 - 11:39:32 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |