Re: Usage of Number type for table columns

On Wed, 15 Aug 2001, Maheswara.Rao_at_Sungardp3.com wrote:

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

Today you know this, but it could change, so allow for it.

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

It sounds like you are going to have intelligence in this key. I would recommend against that one.

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

But Oracle will have a bit of a performance hit when it joins to this table on a number (I assume alot of these columns will be PK's). I believe it is the same penalty that Oracle encounters when it has to query a varchar vs char field, it has to figure out how far to look with the varchar where it doesn't with a char, and the opposite argument also holds, the varchar is much more flexible than the char. So, if these are going to be used to join tables together alot, you might want to consider a straight integer sequence.

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

Once again, it sounds like you have intelligence in these keys.

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

Yes and no. It is ultimately up to the DB to protect itself, but the app shouldn't rely on the DB's protection to get it correct.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Galen Boyer
  INET: galenboyer_at_hotpop.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).