Use of booleans in a database

Question

I'm using Visual Studio 2013, .Net 4.5 and SQL Management Studio 2012. I have a table that tracks offices in my database. On the application side there are a some display rules regarding the layout of offices. If a building is remodeled, offices are renamed, and the old offices can become inactive. It is important to not delete inactive offices for tracking the history items.

I was going to put a boolean column in my database table to decide whether an office is active. If the column isActive equals True then the office will be able to be selected to have items added to it.

One of my colleagues is very much against using boolean fields in the database, but has failed to provide any solid reasons as to why they should not be used. I have been told "don't do it" and "it's hard to use", but nothing that I consider to be really substantial. I think he has run into a situation before where the database was initially setup to have booleans and they customer has added options to a field(something that was true false, can now be A, B or C). It has been suggested that I use a varChar(1) with a "Y" or "N" instead of boolean values.

So what I'm wondering is, given the scenario where there is no way an office can be anything other then "active" or "inactive", is there any reason that representing this value as a boolean should be avoided?

Answer 1

I get your co-workers point about the possibility of some cases where a bool could eventually become more than true or false - in some cases, but not realy this one. But I would very much suggest against going the varchar route. Use either a lookup table with valid values and an integer id with a unique constraint on the valid values, or an enum as it was suggested in the comments/other answers.

we have "seemingly" bool sounding fields all over our database, but are actually string. With our application, which is 4 tier, with a string parameter being passed all the way through the tiers, you have (potentially) no idea what the "valid" values of the string are.

example:

useMaster varchar(1).

I think this fits much better as a bool than a string. It's either "master" or "transaction" in my case. However, because we're using a varchar, what is my proc 4 tiers down expecting to signify master? I have no idea from the UI Is it "Y" for yes? is it "T" for true? If I would have guessed either of those, I would have been wrong, because it was actually "M" for master. In my case we basically have:

If (@userMaster = 'M')
    // use master
else
    // use transaction

None of the choices I had imagined were a valid value to get the master as I wanted. I have to drill down through 4 tiers of code to determine what the proc was actually expecting.

magic strings are bad I would highly advise against using them.

Answer 2

is there any reason that representing this value as a boolean should be avoided?

No.

Given the situation you described ,a boolean is a good candidate for the active/inactive state.

Alternative:

I would however suggest that you use a timestamp/datetime field for recording your active status. You mentioned that you want to keep a history of inactive offices. If you have a column called activeTo with type datetime that allows null values you could use that field to know if the office is still valid.

• null or future date is active;
• any date in the past is inactive;

This would allow you to know when was the office made inactive.

Alternative 2:

You could also keep all your offices in 1 table and have another table to represent the active ones. Just an ID column. You can use JOIN to filter out inactive tables. I have not used this approach so I do not know if there are any issues that I'm missing.

Alternative 3:

As someone else suggested you could add a table to hold all the possible values for the state of the office. This allows you to update the possibilities in many ways and provide the user with a more meaningful representation than just Y or T.

I suggest you look at several options and try to make the best guess you can make. They all have advantages and tradeoffs. Context and balance are important.

Answer 3

The use of varchar(1) for something that is one of two values is daft - that is what a bit field is for.

However, if the requirement ever changes and additional status values are needed, it may be better to use an enum in code and store the value as an integer. Doing it this way means you wouldn't need to go back to change the database structure in order to store the new values.