char(1) vs smallint

I'm having a disagreement with a fellow developer regarding flags. I prefer to declare flag columns as smallint and apply a rule restricting the values to 1 or 0. He prefers to use a char(1) with a rule restricting the values to 'Y' or 'N'. Can anyone give me ammunition against the char(1) or tell me if I'm wrong. Oh yeah, the flag is cast to a boolean in the app written in java, if that makes a difference.Y/N is convenient if users will be querying the database directly, but if you are performing any aggregations then 1/0 allows you to more easily sum the number of flagged records. The percentage flagged, for instance, is just Sum(Flag)/Count(*). (Note that the BIT type won't work with most aggregate functions, otherwise it would be the boolean type of choice.)

You do need to be careful with 1/0 to make sure other applications interpret it correctly. Under some systems TRUE = -1 and FALSE = 0, and other situations are possible.

blindman|||Originally posted by peterlemonjello
I'm having a disagreement with a fellow developer regarding flags. I prefer to declare flag columns as smallint and apply a rule restricting the values to 1 or 0. He prefers to use a char(1) with a rule restricting the values to 'Y' or 'N'. Can anyone give me ammunition against the char(1) or tell me if I'm wrong. Oh yeah, the flag is cast to a boolean in the app written in java, if that makes a difference.
Usually developers know much more than dbas ;). I am using tinyint for flags.|||Thanx blindman and snail! Unfortunately, I'm a developer that new way too much about databases and sql server so I'm a dba now too. As a developer I always said the only thing worse than a dba is a object oriented developer turned dba, guess I'm eating my own words... LOL!!!|||actually, the only thing worse than a dba is a data architect or data modeller like me with years (decades, actually) of modelling and sql language experience, who couldn't solve a performance problem to save his life other than perhaps declaring the obvious indexes...

performance issues aside, you have to look at the implications of your design on the sql to solve business problems

blindman had a superb example -- sum(flag)/count(*)

that's the type of thing a modeller knows, that a dba might not

tinyint (or smallint) is also good because it's a lot more portable across database platforms than boolean

rudy
http://r937.com/|||Those with experience in small shops that required both development and admin duties know best! :D

...but I also think my experience in object-oriented development has helped me develop modular database applications. There is no such thing as bad experience, just people who can't see beyond their own particular project scope.

blindman