7

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?

  • 6
    I'm interested to hear reasons against that. This may be a better post for DBA.stackexchange.com, but I like it here too. I've used boolean fields, and I've also combined many booleans into a single integer field and used SQL to quickly calculate bitwise results. I think your person suggesting a varChar is better than bool is crazy, but I don't have the expertise to give an answer below on why someone would consider varChar superior. – Baronz Mar 10 '16 at 16:04
  • @rogerdeuce when you are trying to describe the technologies you are working with, don't tell us the IDEs, as that doesn't provide much useful information. VS2013 can be used for all the versions of c#, vb.net, asp, asp.net, MCV etc, and I've used SSMS2012 for SQL Server 2005 and above. – user1666620 Mar 10 '16 at 16:12
  • 1
    I imagine posting on DBA.SE will get you much better answers than here, the guys over there are good. – gbjbaanb Mar 10 '16 at 16:22
  • 1
    If you use it, don't let it be nullable. – JeffO Mar 10 '16 at 22:34
9

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.

  • 1
    Using the lookup table or enums, the OP could have an "OfficeStatus" field with valid values "active", "inactive" and later, "will be active in 2 months", "demolished", etc. – Dan Pichelman Mar 10 '16 at 16:48
4

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.

  • 1
    Alternative 2 is probably the most "relational" approach, in the theoretical tradition of Codd and Date. Any fields that are only valid for active offices (not retained for history) would naturally go into that table as well. Of course, just because it's relational doesn't necessarily mean it's a "good" design, but it is sufficient reason to consider it before rejecting it. – Jeffrey L Whitledge Mar 10 '16 at 22:23
  • 1
    +1 for the data field. Somebody is going to ask, "What offices were available at the beginning of the year?" – JeffO Mar 10 '16 at 22:33
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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