Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Constrainting NUMBER

Re: Constrainting NUMBER

From: HansF <News.Hans_at_telus.net>
Date: Thu, 26 May 2005 02:17:10 GMT
Message-Id: <pan.2005.05.26.02.19.53.612689@telus.net>


On Thu, 26 May 2005 08:08:51 +0800, as interested us by writing:

> Is it good to leave all numeric columns as NUMBER instead of specifying a
> precision, say, NUMBER(6,2).
>
> What are the pros and cons of the two approaches? At least, it seems that
> using NUMBER for everything simplified code modification in case we need to
> change the required precision.

Depends entirely on the application.

For example, if you are dealing with a numeric column that is supposed to represent currency for the USofA, what benefit do you derive from allowing more than 2 decimal digits. Conversely, if you do not constrain it at the database, how many places in the application would (should) you do the check?

And - when is an integer not an integer? One answer - when someone slips behind the app using Excel and inserts a non-integer into the data table. (Never happens? It's one of my most frequent 'help the app is broke' calls.)

<warning ... opinionated, cynical, rant follows>

And ... (the list of examples of badness by not constraining at the database goes on for pages ...)

Flip side:

Also,

My conclusion: by all means, put the type check into the code and not into the database. And:

The above is just my opinion, though. I know many developers who have different views.

Why, some developers even ask me to 'just provide a table with a key and a blob ... the application will interpret the meaning of the blob, since there is NO way a database will columns will provide the flexibility we need'. These days I generally oblige - I've found in most cases neither developer nor project will survive to delivery but it ain't worth arguing. I simply document my concern and comply ... <g>

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** I no longer assist with top-posted newsgroup queries ***
Received on Wed May 25 2005 - 21:17:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US