Re: Usage of Number type for table columns

In this case, I think your boss is right. If you feel the need to have some integrity checking at the DB level, use FKs. CHECK and possibly NOT NULL constraints will subject you to the type of application/database maintenance a well-thought out plan should endeavor to avoid. Just be sure that your use of the foreign key is tightly tied to business rules. As an example, a three digit location code in a telecommunications package could tie back to an NPA_NXX_PAIR_CODE table. If for whatever reason this becomes a four-digit number (skip the technical explanations of why that will NEVER happen, just think of the logical approach - we dial 1 to get long-distance, why not a an additional digit(s) to access a region, kind of like what you do now if you use one of those 10-10 numbers), you will still have a valid app (although you may have to do some updates on existing records).

David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002

                                                                                       
                            
                    "Rao, Maheswara"                                                   
                            
                    <Maheswara.Rao_at_Sung       To:     Multiple recipients of list 
ORACLE-L <ORACLE-L_at_fatcity.com>  
                    ardp3.com>                cc:                                      
                            
                    Sent by:                  Subject:     Usage of Number type for 
table columns                  
                    root_at_fatcity.com                                                   
                            
                                                                                       
                            
                                                                                       
                            
                    08/15/2001 10:41 AM                                                
                            
                    Please respond to                                                  
                            
                    ORACLE-L                                                           
                            
                                                                                       
                            
                                                                                       
                            

List,

In one of our applications, my boss wants to define all the numeric columns as NUMBER or leave it as floating point. He wants to define all the tables like this wherever numeric column is defined in the table.

Example:

Location_ID NUMBER

We know, from our application character, that this location_ID would never cross beyond 3 digits. Also, we know that this specific column does not require any digits to the right of the decimal point. ---> (This column is only an example).

He does not want to specify any precision or scale for the number type columns. His point is --- Oracle would use only that much space depending on the actual number of digits he enters into the column. Thus, he is not wasting any space. Also, he says, during the beginning of application, we might not know the maximum limits for number column. Hence, leaving them as
floating point ( Location_ID NUMBER), gives him flexibility and he need not change the number column precision or scale during the entire life cycle of the application. He says, it is upto the application program (JAVA/EJB) to control and check the maximum length permissible against a column depending on the business rules.

I do not know whether this is a correct approach. Intuitively, I feel that this approach is not correct. However, I am not able to come up with any valid reason to negate his approach.

Please inform whether the approach is correct or having any problems, from your experience.

Thanks,

Rao

Maheswara.Rao_at_Sungardp3.com
DBA

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rao, Maheswara
  INET: Maheswara.Rao_at_Sungardp3.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-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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: DBarbour_at_austin.isd.tenet.edu

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-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).