Most people who have a strong programming background know that a bit can have two values, zero and one. We sometimes use this as a flag condition and it can be quite useful. Some others may declare this to be old school when we had lots of processing flags and that it shouldn’t be used, to them I say tough: if a technique works, makes sense, and doesn’t increase overhead or complexity, then go away or I shall taunt you a second time.
With the advent of GUI programming in the ’90s we gained check boxes and toggle buttons. It should be fairly obvious: zero is off, one is on.
Except the actual value stored is usually zero is off, and -1 is on. Off-hand, I don’t know why this is. I knew once upon a time, but the holiday break starts in less than an hour, and I can’t be bothered to look it up.
Microsoft has had a SQL Server problem for a long time. If you create a bit field field, it can store two values, the expected zero and -1. But as a special bonus, it can store a third! NULL! Isn’t that wonderful! So we end up with a triple state binary value.
A lot of people will argue that your data design should not permit nulls, and while I will go so far as to say that primary keys probably shouldn’t allow nulls, but for bits?
This is where you get in to a database design/programming philosophy problem. Should a bit field allow nulls? Let’s name the example bit field in question as [TargetCoordsLoaded] as my current contracting gig is building a missile launch system for a supervillain. If we have a true value, we have the coordinates loaded. If we have a false value, we don’t have target coordinates loaded: probably shouldn’t launch in that condition. But what if the field is null? What does that mean? We’re not sure whether or not coordinates are loaded? Personally I don’t like that uncertainty in a bit. (yes, this is not a great example, you should be able to test the coordinates field for a null or a verified set of coordinates, but it’s a simplified example: live with it.)
The solution that I use is when I create a bit field I’ll create it in SQL Server as not null and put a default of zero on it. Any new records are created as turned off and must be manually turned on. There may be situations where you’d want the default to be on, and that’s fine. One example would be a CurrentCustomer field: when a new customer is created, the field should be on because they’re probably current. But by the same token, you can negate the meaning of the field by naming it LapsedCustomer and default it to off, only turning it on when your contract with that customer is not renewed. You can have a lot of fun on SQL Server Central by posting a question about bit fields by having one field a negated meaning, the other a positive meaning.
Where does Access come in to this blathering? I’ve been doing development in a new version of Access for about a year, and today I saw something new. When you have a checkbox on a form and look at properties, the fourth property is Triple State, and it accepts the values Yes and No. I had never seen that before. When this property is set to Yes, you can allow true, false, and null values to be stored. I don’t have a lot of bits in my current project, regardless I need to make sure that any forms that use them have that property, FOR MY PURPOSES, set to No and a default of zero applies.
If you actually need a tri-value flag where null is not one of the three values, you’ll have to use a tinyint or char(1) with appropriate defaults and constraints. If you need it a lot, create it as a user-defined data type so that you can reuse the code.
But while I’m at it, I’m going to talk about another oddity that bit me in the tuchus not too long ago. I wanted a single byte numeric field. Again, harkening back to ye olde days of programming education, a single byte would typically store a value of -127 to +127. Perfect: I wanted to store the values -1 to 3. Except the tinyint in SQL Server stores values 0 to 255 (and null). So I had to go to the two byte smallint datatype just to store a -1. *sigh* In my data model, -1 and 0, for this particular field, have two different meanings (and a lookup FK table). So two bytes it is.