defining number [message #2430] |
Tue, 16 July 2002 00:29 |
Mikael Wallin
Messages: 2 Registered: July 2002
|
Junior Member |
|
|
I'd like someone with "real" knowledge to explain why i'd design a field number(n,m) instead of just number.
The plain number seems to do the job well.
Likewise... if I use maximum length for Varchar2 will the space used be of that length or of the size of actual data stored in the field?
I have been trying for a year now to get someone to explain why... but it seems like my sources don't really know.
|
|
|
|
Yes but... [message #2449 is a reply to message #2439] |
Wed, 17 July 2002 02:33 |
Mikael Wallin
Messages: 2 Registered: July 2002
|
Junior Member |
|
|
Thanx ... the answer was in the direction I desired but...
I think I need to explain my need... I am a EJB designer and specialiced in the persistent layer.
In my belief the number(n,m) is only a constraint to secure the consistency of the database. Sometimes it is even a information destroyer as I have seen a presentationlayer requirement of 2 decimals result in a number(10,2) which as far as I know rounds the value to two decimals before storing it. (Huge misstake when calculating interest rates)
From my perspective I would love to design a database with just plain number and use the constraint only when specifically needed. (The persistent layer has also a validation sceme)
It is this opinion that I have huge problems to defend against database purists, especially as I suspect there might be something I don't see.
I'd need some help to put the foot down... (Currently writing design guidlines for this)
Why would I use number(10,2) when number does the job well? Data conversion problems? ... robustnes? ... consistency ?... and so forth...
|
|
|
Re: Yes but... [message #2454 is a reply to message #2449] |
Wed, 17 July 2002 11:17 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
As Tom mentions, you only use scale/precision as an EDIT - as in, "I do not want any values in this column with more than 2 numbers after the decimal point".
That is often a valid requirement, as in when storing monetary amounts.
As far as being an "information destroyer", if there are any rounding errors, then the column was incorrectly typed in the first place, right? If I want to store interest rates, I certainly wouldn't use a number(10, 2) to do this.
In the rare case that a column was specified with number(10, 2) and you wanted to make sure that no rounding occurs on an insert (basically rejecting the insert), you could define a before-row trigger that would check:
if :new.some_column <> round(:new.some_column, 2) then
raise_application_error(-20001, 'Illegal value');
end if;
|
|
|