In a job interview I had once, I was asked, “How do you store a Boolean in a database?” My first reaction was to say, True, False, 0, 1, Y or N. However, this question has some depth to it. I do not know why, if you do let me know, but there are no Boolean data type on SQL Server or Oracle. Therefore, we are required to choose our own variable for storing our ‘Boolean’. Some possibilities are an Integer (1 or 0), a Byte (1 or 0), a Char (‘Y’ or ‘N’), a string (“Y” or “N”) or (“true” or “false”) or in SQL Server a Bit. There are many possibilities.
Firstly, don’t be confused about the difference between a Boolean and bool. There is no difference. Bool is simply an alias for System.Boolean, like int is for System.Int32. Bool is just shorter and faster to write. A complete list of .Net aliases and their types can be found here.
I like to write code like:
So how can we store data on a database that would allow for this kind of programming technique? For each case we are required to convert it to a Boolean before using it in the above manner.
Let’s start by using an integer data type. Conversion of Integer to Bool
bool boolValue = (iTrue == 1);
bool boolValue = Convert.ToBoolean(1);
In a real implementation you would not put 1 or 0, you would instead place the value retrieved from the database, flat file or xml resource to make the conversion to a bool.
Using a byte data type is just like with an integer. Conversion of Byte to Bool
bool boolValuBe = (bTrue == 1);
bool boolValueB = Convert.ToBoolean(bTrue);
Using a Char data type is a little different. Conversion of Char to Bool
bool boolValueC = (cTrue == 'Y' ? true : false);
bool boolValueC = cTrue != 'N';
bool boolValueC = !(cTrue == 'N');
And finally, using a string data type. Conversion of String to Bool
bool boolValueS = !(sTrue == "true");
bool boolValueS = Convert.ToBoolean(sTrue);
As you can see there are database considerations and coding considerations required when you want to implement Booleans into your solution. Personally, I like using the Bit for my Boolean storage on the database, it works fine on SQL Server, but have never tested it on Oracle or any other DBMS for that matter. Using a Bit requires no conversion of any kind, simply store it and access it as a bool. If the DBMS does not support a Bit, then I’d lean towards using the Integer because most programmers are comfortable with integers and therefore will have greater acceptance possibility when the solution is proposed. If you are using NHibernate, use the TrueFalse data type, it does the conversion for you.
It was a good question I got in the interview and I under estimated it’s complexity. I will not make the same mistake again. If you read this article then you should not make that mistake either.
Download the source