The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database design - bit, int or char flag for "boolean" fields?

I've seen several ways to identify what amounts to a boolean true/false (or Yes/No, etc.) field in a database:

- Using a bit field, where 0 = False and 1 = True
- Using an int field, where -1 is False and 0 (or anything other than -1) is True; this seems common in Access/VB programmers, because isn't -1 false in VB?
- Using a char(1) field, with Y as True and N as False, and adding a check constraint to make sure the value is either one of those.
- Using a true boolean field type if it's supported by the database (e.g. Postgres)

What is your preference?  My company's supplier uses the 3rd format, and I've typically only used the first, but I've had to do some work with the 3rd thanks to this supplier as well.
Devil's Advocate
Friday, December 19, 2008
 
 
I like to use the first option with a decimal(1) field and 0/1. I don't like Y/N because you also have to worry about character casing.

But unless you have a database that supports a true boolean then it will always be possible to store more than true/false conditions for the field. So pick whatever is supported the best by your provider.
not anonymous
Friday, December 19, 2008
 
 
I thought -1 was true in VB.  Anyway, I have always used 0 for false and COALESCE(var, 0) <> 0 for true in MS SQL Server and also now in Firebird.
Jack Send private email
Friday, December 19, 2008
 
 
If your db supports a bit or boolean type, then that's what you should use. Otherwise, I prefer ints to chars and 0 = false, 1 = true.

Friday, December 19, 2008
 
 
If you choose to use ('N', 'Y') for (false,true) make the best use you can of your DBMS facilities.

For example, constrain the variable (or column) to contain only those two values, and only in upper case.

Some DBMSes will let you use AND OR & NOT with CHAR(1) variables,  and treat 'Y' as true every other value as false. You still have to watch out for NULL.
Walter Mitty Send private email
Saturday, December 20, 2008
 
 
My experience with product management, sales people and prospects is that nothing ever remains a boolean. Scope creep soon changes a yes/no to yes/no/don't know or yes/no/soon/maybe/etc.

So I use a varchar(10) and data types for any attribute that is specified as boolean because sooner or later is going to change.
where have all the remembered logons/passwords gone?
Saturday, December 20, 2008
 
 
Unless the value represented by the field is truly boolean (the light is on or the light is off, with no other possibilities), I tend towards NChar(1) fields and refer to them as a "status column".

If it looks like I'll have a lot of collisions (8 statuses that all want to be the letter 'R'), then I'll look at either widening the column, or normalizing it with an external table & FK relationship.
xampl
Saturday, December 20, 2008
 
 
Using 1 and 0 allows you to easily sum the column in your query to get all the true conditions. Just be sure to use it consistently in your model.
Chi Hoang
Saturday, December 20, 2008
 
 
>My experience with product management, sales people and prospects is that nothing ever remains a boolean.

I read somewhere that the FBI has 10 classifications for a person's sex.

Unknown, Male, Female, Male To Female Transexual PreOp, PostOp, etc etc.

Also, don't slag the managers, as this example shows, the real world is more complicated than you can ever imagine at first.
posting name goes here
Sunday, December 21, 2008
 
 
I prefer to use binary columns, with no nulls. If there is the possibility of it ending up with more than 2 states, then I call it a "status code" and use an integer, and a look-up table.

>My experience with product management, sales people and prospects is that nothing ever remains a boolean. Scope creep soon changes a yes/no to yes/no/don't know or yes/no/soon/maybe/etc.

Correct, we'll end up with stuff like Yes/No/OnlyOnTuesday. 

Or worse, like this list:
http://www.multicians.org/thvv/borges-animals.html
http://www.crockford.com/wrrrld/wilkins.html

ISO 5218 only allows for 4 gender codes:
http://www.govtalk.gov.uk/gdsc/html/frames/PersonGenderCurrent.htm
http://en.wikipedia.org/wiki/ISO_5218
And I seem to remember the FBI used 9 codes, but I can't find any online representation. So 9, 10, whatever.
It's all good.
Peter Send private email
Tuesday, December 23, 2008
 
 
For the FBI, a sex change operation can be relevant when hunting down the bad guy/girl/former-guy/whatever.

For most people, gender is less relevant, and the correct phrasing to put on form letters is all that really matters.


That said, in the case of sex, there's a simple answer: you probably don't really want the "sex" column to be filled with "yes", and thus some form of enumerated type (lookup table, whatever) is probably not going to kill your system.

:)


In general, a question like "what's this entity's X" (where an entity is a row) is rarely answered with a "yes" or "no", so if the column's name is "X" then you need an enumerated type, and if its name is "hasX" ("does this entity have X") then it should be a boolean.

That has the bonus that if the column name is "hasX" then changing it to an enumerated type will seem wrong, so at that point you can have the fun of hunting down every reference to the column. Which you have to do anyway, but if you get compile time and run time errors warning you that it can't find the old column, you have less work to do. And with a decent abstraction layer, that won't take months to do, anyway.

So, the column is "isMale" (or "isFemale") if you want a boolean, and "sex" (or "gender") if you have an enumerated type of some description.

Tuesday, December 23, 2008
 
 
"- Using an int field, where -1 is False and 0 (or anything other than -1) is True; this seems common in Access/VB programmers, because isn't -1 false in VB?"

No.  -1 is true.  0 is false.  0 and -1 are what the relational operators in Microsoft BASIC have returned for a long time.  If you use another value, it is non-zero is true.  This is like C.

Sincerely,

Gene Wirchenko
Gene Wirchenko Send private email
Wednesday, December 24, 2008
 
 
In hex, -1 is FFFFFFF... to however many digits you are interested in. So for 16 bit systems -1 is FFFF.

This explains the mysterious relationship between -1 and true. -1 has all bits set. 0 has no bits set.
yeah baby
Wednesday, December 24, 2008
 
 
I tend to avoid bit/boolean fields.  Invariably, some other status appears.  Also, many databases cannot index a boolean field.  This isn't a huge issue as an evenly distributed data set wouldn't benefit from indexing anyway.  But if one status was <5% of rows, then an index may be beneficial.

The biggest reason to avoid is the inevitable "we need another status" request.  In situations like this, I tend to use a tinyint column in SQL Server (0-255).
Ed
Wednesday, December 24, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz