RE: Storing single numbers in the database

  • From: <Joel.Patterson@xxxxxxxxxxx>
  • To: <rgravens@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 5 Jun 2007 16:26:57 -0400

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@xxxxxxxxxxx 
x72546 
904  727-2546 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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 

Other related posts: