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.
"Was that select all from customers or was it select all from customer?"
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:
BASE_CUSTOMS_DUTY_AMOUNT NUMBER 10,2 BASE_CUSTOMS_DUTY_AMOUNT NUMBER 12,4 STOCK_COUNT NUMBER 10,0 STOCK_COUNT NUMBER 9,2A 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.
CATEGORY CHAR 3 CATEGORY VARCHAR2 3There is a mixture of both CHAR and VARCHAR2 in the database. This could lead to inconsistent behaviour between attributes.
DATA VARCHAR2 256 DATE1 DATE 7 DATE_DATA DATE 7 FIELD1 VARCHAR2 8 FIELD2 NUMBER 1,0 FIELD3 NUMBER 9,0
FLAG VARCHAR2 1This attribute appears in more databases than I care to remember. What does flag mean?
GL_REFERENCE_ VARCHAR2 18No this is not a mistake, the last part of the name is missing.
STATE_CD VARCHAR2 3 STATE_CODE VARCHAR2 4An 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 20Oracle 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,0It 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)
decode(data_type, 'NUMBER',data_precision||','||data_scale,data_length) attribute_length,
WHERE owner = 'XXXXX' -- Target Schema Goes Here
GROUP by column_name,
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