PIPER-Rx - Attribute Naming

PIPER-Rx
Oracle E-Business Suite Resource Centre

Attribute Naming: "What standard do you use"?

Attribute Naming: "What standard do you use"?

Whilst this tip is not directly related to Oracle E-Business Suite it is included because it appeared in prior web pages and has been referenced in the book by John Day and Craig Van Slyke: IMPLEMENTING DATABASES IN ORACLE 9I ISBN:1-57676-082-0


It is not my intention to define an attribute-naming standard, but to aid you to identify where standards may not have been adhered to.

This article seeks to assist identifying inconsistencies in column naming and / or data types within a target application. It is generally accepted that failure to adhere to a standard, regardless of what that standard is throughout an application or group of applications, will cause problems during both application development and post implementation maintenance stages of the application life cycle, resulting in loss of productivity e.g.

It is often useful not to have a working knowledge of the target application when undertaking this task as familiarlity often leads you to tend to explain away the inconsistencies rather than question the developers.

Try not to be heavy handed when using this information with developers, but highlight where there may be inconsistencies. The fun part is the number and varied reasons for the inconsistancies you encounter!

What we are looking for in this exercise are:

The following are some examples taken from a single application:

ATTRIBUTE SIZING:

          BASE_CUSTOMS_DUTY_AMOUNT       NUMBER    10,2 
          BASE_CUSTOMS_DUTY_AMOUNT       NUMBER    12,4

          STOCK_COUNT                    NUMBER    10,0
          STOCK_COUNT                    NUMBER     9,2
  
A mixture of sizing could cause inconsistent behaviour in both screens and reports, in the case of stock count there appears to be an accuracy issue.

DATA TYPES:

          CATEGORY                       CHAR      3
          CATEGORY                       VARCHAR2  3
  
There is a mixture of both CHAR and VARCHAR2 in the database. This could lead to inconsistent behaviour between attributes.

AMBIGUOUS ATTRIBUTE NAMES:

The next examples are attributes where the naming does not appear to describe the intent or content of the attribute regardless of the entity (table) name to which they belong. There is also some inconsistency in data type and sizing
          DATA                           VARCHAR2  256
          DATE1                          DATE      7
          DATE_DATA                      DATE      7
          FIELD1                         VARCHAR2  8
          FIELD2                         NUMBER    1,0
          FIELD3                         NUMBER    9,0
  

OTHER:

Now for my all time favourite!
          FLAG                           VARCHAR2  1
  
This attribute appears in more databases than I care to remember. What does flag mean?
         GL_REFERENCE_                  VARCHAR2  18
  
No this is not a mistake, the last part of the name is missing.
         STATE_CD                       VARCHAR2  3
         STATE_CODE                     VARCHAR2  4
  
An implied naming standard does not appear to have been adhered to, also Australia state codes are a max of 3 characters.
         TIME_STAMP                     DATE      7
         TIME_STAMP                     VARCHAR2  20
  
Oracle has always been strong with dates, but a mix of date and varchar types… Also the current releases of Oracle have implemented a data type of time_stamp
         YEAR_NUMBER                    NUMBER    4,0 
         YEAR_NUMBER                    NUMBER    6,0
  
It appears this developer wanted the application to last to the year 999999!

As I have said, there could be any number of valid reasons for some of the above inconsistencies. But if you only find one that needed to be corrected or was an oversight, running this report was worth the time. I used the following script to produce the report. (NOTE: You will need to change the WHERE clause for the target schema)

    SELECT column_name, 
           data_type, 
           decode(data_type, 'NUMBER',data_precision||','||data_scale,data_length) attribute_length,
           count(*) occurances
      FROM dba_tab_columns
     WHERE owner = 'XXXXX' -- Target Schema Goes Here
     GROUP by column_name, 
              data_type, 
              decode(data_type, 'NUMBER',data_precision||','||data_scale,data_length) ;

  
A simple "Attribute Naming" report that lists the column attributes for a selected schema can be found in the "Other" section of the free reports page.

Last update: May 2009