Re: Storing single numbers in the database

Sandra Becker wrote:
> 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)?
>

After reading all the comments, I thought of something which may make a big difference. And it is late at night, and I don't have the energy to be precise, so please try to fill in the blanks I will leave.

One of the comments suggested that this "developer" may think that 1 and '1' are one and the same. This is of course true in any number of scripting languages; the language converts text to numeric back to text depending on what it hopes you want it to be at any given moment. But

In these languages, there is usually something like a boolean. But not a real Boolean. Just 1 and everything else (unless it is zero and everything else. It is late, and I've been too Oracle Centric for Too long).

Of course, real scripters love to write expressions such as

        IF (expression) THEN DO SOMETHING THAT CAN TOTAL THE COMPANY

where you hope the expression will evaluate to zero or not zero. And in this case, whether you have 0 or '0' may make a big difference.

And if someone understands what I was trying to say, and it turns out that what I am suggesting is really impossible, please let me know by private mail.

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