RE: Storing single numbers in the database

I also agree that the datatype should represent what is being stored. And indeed if 0 and 1 are representing true or false, I believe it would be faster to treat them as integers. 00000000 000000001 (pseudo storage). As to casting them??? What?.  

BTW Tom Kyte in his new book Oracle Database Archetecture recommends to almost always (read always) use varchar2() instead of char(), even when it is of length 1... page 498...  

I'll skip the explanations... and jumpt to the final paragraph.

It is for these reasons - the fixed-width storage, which tends to make the tables and related indexes much larger than normal, coupled with the bind variable issue-that I avoid the CHAR type in all circumstances. I cannot even make an argument for it in the case of the one-character field, because in that case it is really of no material difference. The VARCHAR2(1) and CHAR(1) are identical in all aspects. There is no compelling reason to use the CHAR type in that case, and to avoid any confusion, I "just say no," even for the CHAR(1) field.  

Joel Patterson
Database Administrator
joel.patterson_at_crowley.com
x72546
904 727-2546

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rumpi Gravenstein
Sent: Tuesday, June 05, 2007 3:34 PM
To: Oracle-L
Subject: Re: Storing single numbers in the database  

I'm with Bryan. The rule of thumb I've used is that if you are not going to do math with the value it should be stored in a character field. I've stored phone numbers, zip codes, SSNs etc. in CHAR and VARCHAR2 fields. The point behind some of this is that as these "numbers" can have significant leading zeros they are much easier handled as strings. Also, with strings there can be no rounding and other display related "challenges". In your situation these benefits are not as clear. At the end of the day you should store the field in a string type as there is no telling what the future will hold.

On 6/5/07, Bryan Michael Lenihan <bryan_at_orixcorp.com> wrote:

Sandy,  

I actually kind of agree with the developer - from what you have posted. I typically recommend CHAR/VARCHAR for numeric values that are not going to be used in a calculation and allow the developer or a check constraint to set the business rules. Even more so if the entry is going to be an unlinked code (0,1) for flexibility and have the developer, or check constraint, set up the business rules.  

I do not really agree with the translation issue that the developer has posted, but I do not know what language or system you are using.  

This is just my 2 cents worth from your description.  

Thanks,

Bryan

(DBA turned Developer turned back to DBA)      

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sandra Becker
Sent: Tuesday, June 05, 2007 2:08 PM
To: oracle-l
Subject: Storing single numbers in the database  

Linux RHEL4, Oracle 9.2.0.8  

I have a developer who wants to define a column as CHAR(1) in an existing table. The only values this will ever hold are zero and one. I asked why he didn't define it as NUMBER(1) and the response was "Because I don't want to do the translation in my code." He believes that the column in this table will be heavily used when it gets to production. Since it's a new feature for the application, we have no way of knowing for sure how the customers will use it. They're kind of funny about deciding for themselves how they want to use the features.  

Questions: Does it really matter if it's stored as NUMBER(1) or CHAR(1)? What are the ramifications, if any, of defining the column as CHAR(1)?   I personally believe that if you want to store a numerical value and ONLY a numerical value in a column, you should define the column as NUMBER.   Sandy    

-- 
Rumpi Gravenstein 


--
http://www.freelists.org/webpage/oracle-l