March 4, 2015 at 1:35 pm
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
March 4, 2015 at 3:15 pm
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
March 5, 2015 at 4:26 am
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! 🙂
March 5, 2015 at 4:34 am
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?
😉
.
March 5, 2015 at 11:41 am
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.
----------------------------------------------------
March 5, 2015 at 11:44 am
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
Change is inevitable... Change for the better is not.
March 5, 2015 at 12:44 pm
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.
March 5, 2015 at 12:52 pm
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! 🙂
March 5, 2015 at 3:26 pm
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:
.
March 5, 2015 at 3:33 pm
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 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply