Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: 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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: char(1) vs smallint

    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.

  4. #4
    Join Date
    Sep 2003
    Posts
    364

    Talking

    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!!!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Those with experience in small shops that required both development and admin duties know best!

    ...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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •