What is the equivalent to Yes/No data type in SQL?

  • Sigerson

    Hall of Fame

    Points: 3697

    Jeff Moden (3/3/2015)


    CELKO (3/3/2015)


    While what you say is absolutely true, there are so many nice ways to say this and so many ways to make this useful and you managed to avoid all of them.

    I gave link to a detailed article; that seems useful to me. I do not think it is useful to lie to people or not cry out a warning when they are in dangerous territory. :satisfied:

    Understood but you always seem to put the really hard edge on things. Folks might be more enticed to read your articles if you led them into it. That particular article is a good one, indeed.

    +42

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • TomThomson

    SSC Guru

    Points: 104707

    CELKO (3/4/2015)


    I did like the article basically promoting thinking things out at design time to see if the state you want to represent with a BIT data type is truly a yes/no type (no gray areas or future adjustments with more BIT fields).

    I think the real problem is conceptual. Our design principle in SQL is that all data types in SQL have to be NULL-able. With a BOOLEAN, I am immediately plunged into a logical system with {TRUE, FALSE, UNKNOWN, NULL} primitives. Standard SQL has “x IS [NOT] NULL” and also has the “x IS [NOT] [TRUE | FALSE | UNKNOWN]” predicate that nobody implements. This leads to contradictions or an over-complicated mess of rules to compensate for the flaws.

    It is utterly wrong to call the truth values in a three-value logic "BOOLEAN". BOOLEAN logic has only two truth values. Logics with three values are called trivalent (or ternary). The trivalent domain has 19683 diadic operators, as opposed to the BOOLEAN domain's 16 (of course we tend to work with only 8 of the 16 boolean perators, and 9 of the 19683 diadic trivalent operators (to enable both Kleene and Lukasiewicz systems to be described). There is no problem at all with building a trivalent type to handle the truth values of trivalent logic, and if NULLs are reuired that's fine - something can be known to be "true", or known to be "false", or known to be "unknown", or we may not know which of these three values it currently has. If some bloody fool attempts to represent the truth values of a trivalent logic using the BIT type and treating NULL as the third value that isn't a problem with the BIT type or with trivalent logic, it's a problem with allowing bloody fools to design things beyond their mental capacity to understand. I have seen students get into the sort of twist that you are pushing here, and invariably it's because they haven't grasped that the truch value "UNKNOWN" is a perfectly good value and it's perfectly possible for it to be known that some atribute has that value - and I can't see that your statements display any less confusion than that.

    I don't believe there is any design principle in SQL that states that all values are NULLable. The relational principles allow domains to be constrained by domain constraints, and a constraint that excludes NULL from a domain is perfectly reasonable provided that people using the resulting domain are aware that any attribute in that domain has to be known, it can't be NULL.

    The simple “yes/no” is the answer to a question, while a database holds facts. (see Chris Date).

    If you think Date is a useful authority, will you agree with hime abot NULLs? Certainly everything you've said in this discussion is in direct contradiction if his views on that topic. Personally I believe his views on NULLs are thoroughly stupid, and the nonsense he spouts on that topic makes me reluctant to rely on any of his ideas.

    I used to do statistics for a psychological testing unit in a state prison system. The “yes/no” options do not really work. We needed a NULL for “not answered” (or an explicit “prefer not to answer” you see so much today), some way of handling “Not Applicable” and another way to handle contradictions. The physical check mark made with #2 pencil in a box on a scantron form is not the fact we really want to put in a database. BIT sucks for this level.

    We had to get software for handling this stuff and set up business rules like “substitute the [average | median | constant] for missing values here” or “validate these answers against the prior test” and so forth. And that just got us to the raw data! :w00t: After that, I had to get usable information with statistical analysis.

    Yes, it's easy to point out that the BIT type is not a suitable type to use for a domain which needs to have a large number of distinct values (at least for meanings of "large number" that exclude numbers smaller than three). It's also easy to point out that it is utterly hopeless to try to use CHAR to represent a domain that needs to contain more than 256 distinct characters. That doesn't mean we should throw CHAR away and insist that all characters are NCHAR - and equally the corresponding difficulty with using BIT in cases for which it is not suitable is not a good reason for throwing away BIT and insisting on using something else simply ecause it is not a universal panacea.

    Tom

  • Lempster

    SSCoach

    Points: 15376

    TomThomson (3/4/2015)


    There is no problem at all with building a trivalent type to handle the truth values of trivalent logic, and if NULLs are reuired that's fine - something can be known to be "true", or known to be "false", or known to be "unknown", or we may not know which of these three values it currently has.

    I don't know about trivalent, that sounds like Rumsfeld logic to me! 🙂

  • Tim Walker.

    SSCertifiable

    Points: 5113

    I have been trying to work out whether this thread is useful to me in a business context.

    But is it a straight yes or no?

    😉

    .

  • MMartin1

    One Orange Chip

    Points: 27375

    One thing I can say for the BIT type .... I can see the issues arising from not knowing the yes/no answer at time t1. So these were allowed to be NULL. The value will depend on a series of future events. For example, are a set of transactions subject to taxable income in their tax year? They may be now but that can change in the future depending on if (say) I find a tax preparer that can wiggle me out of them. This is a case where I dont need a tax bracket, just if something is taxable, as a tax bracket is something the preparer handles so saving me the complexity. I say this is a good case for a BIT field that is nullable until a final answer presents itself.

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

  • Jeff Moden

    SSC Guru

    Points: 993628

    Tim Walker. (3/5/2015)


    I have been trying to work out whether this thread is useful to me in a business context.

    But is it a straight yes or no?

    😉

    Heh... "It Depends". What kind of Yes or No are we talking about? :hehe:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Lynn Pettis

    SSC Guru

    Points: 442091

    Jeff Moden (3/5/2015)


    Tim Walker. (3/5/2015)


    I have been trying to work out whether this thread is useful to me in a business context.

    But is it a straight yes or no?

    😉

    Heh... "It Depends". What kind of Yes or No are we talking about? :hehe:

    Won't handle the typical "yes, but ..." or "no, but ..." answers you get from kids and politicians when asked a simple question that can be answered with just a yes or no answer.

  • Lempster

    SSCoach

    Points: 15376

    Lynn Pettis (3/5/2015)


    Won't handle the typical "yes, but ..." or "no, but ..." answers you get from kids and politicians when asked a simple question that can be answered with just a yes or no answer.

    In the Data Warehousing world that would be a slowly (or in this case rapidly) changing dimension! 🙂

  • Tim Walker.

    SSCertifiable

    Points: 5113

    Jeff Moden (3/5/2015)


    Tim Walker. (3/5/2015)


    I have been trying to work out whether this thread is useful to me in a business context.

    But is it a straight yes or no?

    😉

    Heh... "It Depends". What kind of Yes or No are we talking about? :hehe:

    OK. Maybe about 255 shades of grey in the answer. And some people don't know. Still can't decide on the data type !!! ... :w00t:

    .

  • Tim Walker.

    SSCertifiable

    Points: 5113

    Lynn Pettis (3/5/2015)


    Won't handle the typical "yes, but ..." or "no, but ..." answers you get from kids and politicians when asked a simple question that can be answered with just a yes or no answer.

    Ah. politicians!

    We have an election upcoming in the UK, and I don't think the datatype will matter providing it is nullable, as they don't know anything.

    Kids on the other hand see most things in black and white, so a Boolean answer will be fine.

    Unless the question is 'what did you do at school today', in which case we are back to NULL again (or more specifically nothing).

    :laugh:

    .

Viewing 10 posts - 31 through 40 (of 40 total)

You must be logged in to reply to this topic. Login to reply