Hello,

I have a Table with some char(1) columns. Allowed values are y,n and NULL. I consider to change to bit. My professor told me not to do, because of some kind of incompatibility. Did'nt really get what he meant. Is that true? Are there any issues about this?

Thank you!

one incompatibility is that it is not portable

some database systems do not support a BIT datatype, but they all support CHAR

in other words, if you build a database using BIT, and then you wish to move it to some other system, you may have to change BIT anyway

CHAR(1) and TINYINT are the two datatypes most often used

another benefit of CHAR(1) and TINYINT is flexibility and this is illustrated by the following scenario

imagine we have a BIT column called MaritalStatus, with values 0 and 1 meaning Single and Married

actually, we wouldn't, because that would be misusing the BIT datatype, which is really supposed to be used like a switch -- on/off, yes/no, 0/1

so we would have to name our BIT column IsMarried

now, would we also have another column called IsSingle? probably not, but you can imagine the programming code either way...
and then one day, we are told to change our application to incorporate divorced as a valid marital status

if we had started out with TINYINT values of 0 and 1, we could simply add 2

would any of the code need to be changed? yes, if when we wrote the code we pretended that there would never ever forever never be more than two values

Thanks for the example. Do you think it makes sense to convert from y/n to 1/0 ?

no, i would just leave your CHAR(1) the way it is

TINYINT is not standardized SQL either. So that is also not portable.

you are absolutely right

so what do you use for this, SMALLINT?

or CHAR(1)?

I'm in favor of CHAR(1) for many reasons, but the problem is that most people use mneumonic values like "Y" for yes and "N" for no, and those values are very locale/language specific. You also run into problems where you have mneumonic conflicts, where you'd have SINGLE, MARRIED, DIVORCED, STUPID (remarried) where the obvious answer causes a mneumonic clash (the S for single and the S for stupid conflict).

I have a very strong tendancy to use a foreign key to a lookup table for lookups like this... That allows me to validate the incoming data (so that the code can only insert values in the lookup table), and to extend the set of valid values by simply adding a new row to the lookup table. If you choose to get fancy about things, you can carry the lookup to a language/lookup table to allow you to store multiple language descriptions (via a one to many relationship) for your lookup values.

-PatP

it's even worse in my case: STUPID SQUARED (married twice, divorced twice)

Yes, SMALLINT is in SQL:2003.

fascinating, thanks

do you happen to have copies of the standard(s)? i personally don't, and i would enjoy knowing someone who does

Yes, I do. I just can't pass them along for copyright reasons.

thanks

i wasn't asking for copies -- i just wanted to know whom to contact when i have a particular question about the standard (which is not often)

for example, which of the following is valid ...

select *, 23 from t

select t.*, 23 from t

select 23, t.* from t

1 is invalid. An <asterisk> must either be qualified (as in 2 and 3), or it must be the only expression in the <select list>. 2 and 3 are fine. (Subclause 7.23, <query expression> in SQL:2003)