Naming conventions in Oracle V7 envt (long)

From: Stephen W. Thompson <st_at_blue.seas.upenn.edu>
Date: 30 Nov 1994 00:30:00 GMT
Message-ID: <3bgh28$dd6_at_netnews.upenn.edu>


In January 1993 Iraida Becerra (ICBHC_at_CUNYVM.BITNET) wrote to Oracle-L looking for people's experience with naming standards for use with Oracle. (See below.) She got one relevant reply, from Mike Oswald (MOSWALD_at_UNMCVM.BITNET). (Again, see below.) I want to revisit their thread.

In my experience naming standards can provoke strong feelings in some people. Additionally, despite anyone's strong feelings about the naming standards that 'should be' used, I buy the notion that naming standards can't be universalized, but instead must match needs of organization using them. (See "Guide on Data Entity Naming Conventions", Judith J. Newton, National Bureau of Standards #500-149, Oct. 1987.) So here I'm interested only in the limitations imposed by using Oracle as the RDBMS, in our case using Oracle V7, Forms 4, CASE 5.0 and 5.1 and Financials (Government version).

MY QUESTION:
In this environment, what naming rules will help us and prevent problems the best?

SPECIFIC AREAS, WITH EXAMPLES AND QUESTIONS What follow are objects for which we're seeking to standardize names, accompanied in some cases by examples, relevant limitations and issues.

  • TABLES & VIEWS Oracle courses : Singular entity name: STUDENT CASE use : Plural entity name: STUDENTS Our present ad hoc standard: <= 25 chars, leave 5 chars for prefix for use in views, no other restrictions My suggestion : ?? Mike Oswald's use : 2 char 'owner code' + name + 'T': OP_MENU_ CATEGORIES_T is owned by Operations
   Issues                     : Should table owner be part of name, or is
                                OWNER column in ALL_TABLES sufficient?


* VIEWS
Oracle courses : Table name + "VUnn" suffix: STUDENT-VU01 CASE use : ?? Our present ad hoc standard: Table name + meaningful descriptive phrase + "V" ("DEGREE_ALLSCHOOLS_V") My suggestion : ?? Mike Oswald's use : 2 char 'owner code' + name + 'V': OP_MENU_ CATEGORIES_V with Operations as owner Issues : Since our security solutions have most people reading from views rather than the tables themselves, is it really helpful to flag views differently from tables (with a 'V' as opposed to a 'T', for example)?
* COLUMNS (WITH PRIMARY KEYS AS A SPECIAL CASE)
Oracle courses : For primary keys, "ID" in own table, "_ID" suffix as foreign key: ID, STUDENT_ID For dates, "DATE_" + past participle: DATE_SHIPPED CASE use : No limitations Our present ad hoc standard: <= 25 chars, leave 5 chars "in case" My suggestion : Don't flag primary keys (so DEPT_CODE will be the same as primary key or foreign key, rather than DEPT_ID 1 place, DEPT_CODE another). Mike Oswald's use : ?? Issues : Include table/entity name or not? ("STUDENT_ FULL_NAME" or "FULL_NAME"?) How to indicate primary keys if multi-column?
* TABLE ALIASES
Oracle courses : Not used (use full table name instead) - at least in some books. CASE use : ?? Our present ad hoc standard: First 3 chars of single-word table names, first char of each word (max 3) of multi-word names My suggestion : ?? Mike Oswald's use : ?? Issues :
* CONSTRAINTS
Oracle courses : Table name + ( PK, NN, FK, or UQ ): PERSON_PK CASE use : ?? Our present ad hoc standard: ?? My suggestion : Oracle's method Mike Oswald's use : ?? Issues :
* INDEXES(INDICES)
Oracle courses : "_IX" suffix CASE use : Same as constraint on column Our present ad hoc standard: table alias + column name + "_I" My suggestion : ?? Mike Oswald's use : 2 char 'owner code' + name + 'In' ("CI_CUSTOMER_I1" with Customer Information the owner) Issues :
* ROLES
Oracle courses : ?? CASE use : ?? Our present ad hoc standard: "_ROLE" suffix My suggestion : No suffix Mike Oswald's use : ?? Issues : Will they ever be used when it won't be obvious that they're not roles?
* SYNONYMS
Oracle courses : ?? CASE use : ?? Our present ad hoc standard: ?? My suggestion : ?? Mike Oswald's use : Same as table name Issues :
* APPLICATIONS (an ORACLE*Case object)
Oracle courses : 4 chars max CASE use : 4 chars max Our present ad hoc standard: ?? My suggestion : ?? Mike Oswald's use : Not mentioned Issues :
* SEQUENCES
Oracle courses : Column name as for foreign key ("S_CUSTOMER_ID"). CASE use : ?? Our present ad hoc standard: ?? My suggestion : ?? Mike Oswald's use : 'S' as prefix ("CI_CUSTOMER_S") Issues :
* ABBREVIATIONS
Oracle courses : Liberal use ("PCT" for percent, "EMP" for "EMPLOYEE", "ORD" for "ORDER" CASE use : ?? Our present ad hoc standard: ?? My suggestion : Non-abbreviated words as well as abbreviations from a centrally controlled list Mike Oswald's use : ?? Issues :

OTHER ISSUES THAT MIGHT AFFECT DECISIONS Does the name need to be unique throughout the entire Oracle DB instance?   Or in combination with OWNER, TABLE and other columns in tables such as   ALL_OBJECTS can objects with identical names be readily distinguished? Will abbreviations be avoided or standardized to make use of %<string>%   searching?
Will we be making use by query tools (GQL, Powerplay, Business Objects,   Browser, etc.) easier?
Will we be consistent with conventions used in Oracle CASE? Will the name be helpful in the most-used cases? (Benefit/Cost argument) Does the name balance providing information versus ease of keying for   programmers and others?
Will the name be seen more often in sorted order or by searching for a   substring ("%<string%")? (So, does it matter if a keyword appears at   the front of the name or not? When using query tools such as Browser   will table and column names be displayed in alpha order or random   order, and can the set that is displayed be selected using wild   cards?)

Thanks for any ideas or discussion,
Steve

--
Stephen W. Thompson, U. of PA, Data Administration, 215-898-1236
E-mail (MIME): thompson_at_pobox.upenn.edu (http://www.seas.upenn.edu/~st/st.html)
(For security matters, use security_at_isc.upenn.edu, read by Data Admin. staff)
** Being angry doesn't mean you hate someone; it just means you're angry. **

PREVIOUS MESSAGES

Iraida's message (in part):
> I would like to ask if anyone would share the criterions that he/she
> has followed to name different database objects such as: tables,
> columns, sequences, indexes, clusters, and so on. It also would
> be very helpful if anyone knows of a text-book regarding naming
> conventions.

Mike's response (in part):
> Iraida...we currently have several standards and they as follows....
> 
>   Table Names:  OP_MENU_CATEGORIES_T
>                 |                  |
>                 |                   \ The last character could indicate
>     The first character denotes       whether it is a T(able), V(iew) or
>     the area responsible for that     S(equence number).
>     table (or the area the table
>     was created for).                 In the case of I(ndexes) you will
>     O = Operations/Network Support    see names like: ci_customer_in
>     A = Academic/Customer Support                     |_________| ||
>     etc.                                             /           //
>                                       project table name    i = index
>     We have tried to adhere to this                         n = 0,1,2,3,...
>     but recently I created a series of
>     tables starting with CI_ (customer
>     Information tables).
Received on Wed Nov 30 1994 - 01:30:00 CET

Original text of this message