Why I don’t use binary fields in databases

This is a debate that seems to come up way too often – about using binary fields in databases. When you’re young and hungry and all excited about all the field types in a relational database, you feel like they all have a place, so you need to use them all. They’re like Pokemon…

And so you’ll have some business-oriented data field that’s either “yes” or “no” and you think “Aha! I’ll use the bit field!” and move on. You have now set your claymore mine, pointing right at your foot.

Best case – the business software authors know better an use an Int of some flavor, and the report writers treat the bit field like an integer field that can be either 1 or 0. Worst case is that everyone codes it as a boolean field, from your back end all the way to the front. (This is most likely when “everyone” is you, because you’re the sole developer on the project)

Now at some point it’ll happen – the customer or expert who owns the business system will come to you and say:

“I know we told you that that field could only be ‘yes’ or ‘no,’ but we need to indicate if someone leaves it blank.” or “I know we told you we only needed ‘male’ or ‘female,’ but it turns we also need to be able to put ‘Not answered,’ ‘Transgender Male,’ ‘Transgender Female’ and a few others.”

Well, if the best case scenario happened, hopefully you can either convert the bit field to an Int directly, or perhaps create a replacement field and migrate data. However, if that binary definition for the field is riddled throughout the system, you’ve got a lot of work and testing ahead of you.

If you’re talking about real-world data, there are never “only two options.”

Now one disclaimer – with massive amounts of real-world data, you may run into storage concerns with respect to using one bit vs. 32 bits in an Int. So there may be cases where you do actually make a considered decision to use a bit for space considerations. But it should always be a careful, informed decision, not a default.