Re: Constrainting NUMBER

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:

• haven't tested in Oracle, but typical code might take a whole bleem to do the conversion for each and every data entry. Therefore, constraining the data type at the database might have a serious impact on performance ... possibly by adding as much as 1/1000th of a second to an operation. (I guess it's an expensive way to guarantee valid data, if you compare to hand coding and testing each occurance in the app.)

Also,

• when trying to beat the clock for release, putting in constraints is definitely working against you ... (better to code them up later when the app is released);
• prototype and demo code is much easier to present and change if there are no constraints. (Same goes for error handling. Much better to deal with BSoDs after the contract has been signed.);
• constraints, like assumptions, should be documented. Think about the amount of documentation that can be eliminated by not having data type constraints in the database;
• studies prove that it less expensive for the app developer to avoid doing it right the first time (especially if the customer can usually be encouraged to pay for upgrades and bug fixes. A whole industry around at least one major company have learned THIS one well.);

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

• make sure you get it into ALL the code;
• make sure that noone can bypass the code and get data into the database that will crash the code from the back end;
• make sure that the change is conistent at all times; and finally
• never use database object types to handle specialization

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