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