Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

[backstory] I have a simple database of our current Widget inventory. It averages just dozen columns across five or six tables, but has a huge number of records already.

Some Widgets came with caps, some did not, but we've actually never kept track before. Now management wants users to be able to keep track of whether each individual Widget arrives with a cap or not. We will not be doing an inventory to check our current stock, but when I add the new column for "Arrived Capped," there will be no data on everything received before today. What is the usual, best-practices approach to scenarios like this?

When adding a string column, the meaning of "" is obvious; no data has been entered. But I'm adding a bool, thus existing records will default to a value that does indicate something: FALSE.

My first thought was to make the user portal have, rather than a checkbox, a radio button pair for this question. Upon creating a new record, or even returning to old records with false-falses, if they click Yes or No, that value is recorded, plus another bool indicates this question was actually answered manually. That is; if the first bool is F, but the second bool is T, then the first bool is not a 'default false'.

Their hand-waving solution was to have records dated before today displayed as "unknown" (this was nixed as existing records could not then manually be given a user-verified value of false). Should I use a faux-bool int and think of it in trinary? Or, back to using a separate tracking bit/bool, should I make a new table for that sort of data? Is this a common concept? Any naming conventions?

share|improve this question
    
My first post, plus (how did you guess?!) I'm not a dba by any stretch. Thanks for being gentle. –  Joel Reid Mar 15 '13 at 14:37
    
'' does not mean "no data has been entered". It means an empty string has been entered. "no data" is indicated by a NULL value (unknown). –  a_horse_with_no_name Mar 18 '13 at 7:56
add comment

2 Answers 2

up vote 4 down vote accepted

In most DBMSs, or at least the ones I have dealt with, a Bool is really a trinary. You have 1, 0 and NULL. NULL is specifically there for "I haven't entered any data yet." Depending on how you code it your checkbox should default to unchecked for 0 or NULL. Then 1 for checked of course. However in your reporting you can actually differentiate between rows where someone has looked at it and entered a value (1 or 0) and rows where no one has updated it yet (rows where the value is NULL).

share|improve this answer
    
Ternary information springing from a boolean datatype irks something deep in me. I'll get over it! -- Thanks! –  Joel Reid Mar 15 '13 at 17:08
1  
I agree with you. However don't look at it as trinary information. Look at as binary information or a lack of information :). It's really the same thing and you can take advantage of it, but that seems to be how DBMSs were designed. –  Kenneth Fisher Mar 15 '13 at 17:13
    
@KennethFisher Another way to look at it is that you have three values, TRUE, FALSE, and UNKNOWN. One of the nice thing about trinary logic is that you can answer questions like "The sky is blue AND Schroedinger's cat is dead." The answer of course is UNKNOWN. –  Chris Travers Mar 16 '13 at 8:20
add comment

When Codd formalised the null marker he wrote that it is a way of dealing with MISSING and INAPPLICABLE data. What you have here is a clear case of MISSING.

As Kenneth correctly points out, it is not really a tri-predicate system, it is a bi-predicate system where you might not actually know the value. The null is not a value itself - it is a marker that the actual value is not available.

Your uder interface, however, needs to display the 3 states yes, no and don't know.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.