For work I’m writing a proposal on database usage, about which data types we should use in what situation. That has me stumbling upon an interesting problem. Let me explain.

BIT in SQLyog

There are situations in data where records need to have some sort of flag, like “deleteAllowed” or “activeUser”. That would have one of two possible values: 1 or 0 (yes or no, true or false, etc). You might ask yourself “what’s the fuzz? There is a BIT data type, why not use that?” We did consider using it and it should be the best option, however the value is a true system bit which can’t be properly displayed by the MySQL manager we use (and love by the way!), SQLyog. The problem is it displays a square representing a bit-value of 1, or a text that it contains a binary value or image, meaning 0 (illustration). This is not really workable as it’s easy to mix them up and the way they are presented varies in different versions of SQLyog.

So, what other options do we have? We have ofcourse the infamous VARCHAR(1) UNSIGNED alternative. But that’s not a real solution in my opinion. Just think about it for a second. What do we want? 1 or 0. What do we get? 0, 1, 2, …, 255. It will ofcourse work, but if you love strict data modelling your hair would reach for the sky right now.

Third and final option: ENUM(’1′,’0′). If we use this you should expect to have only the two given options. This is nice since we don’t have to use 1 or 0, we can also say yes/no or true/false. Really neat if you ask me. Unfortunately, ENUM has a drawback: it not only listens to the possible values, it also accepts indexes. “Why is that a bad thing?” you may wonder. I’ll tell you. The indexes go from 0 to n+1. The first item in the ENUM has an index of 1, not 0. “But what about index 0?” Don’t ask me why, but it’s an empty string. Example:

/* We have the following table "test" */
FIELD  TYPE             NULL
------ ---------------- ------
id     INT(10) UNSIGNED NO
active ENUM('1','0')    NO
 
/* And we execute the following query */
INSERT INTO test (id, active) VALUES (1, 0);
 
/* We would have this record */
id active
-- ------
1

So, it’s still able to hold an unwanted value. And you can’t disable or work around it as far as I know.

So I guess the best solution is still using a BIT field, even though it makes life a little more difficult. But then again, data modelling and logic should be challenging, otherwise it wouldn’t be so interesting and fun, would it?