What to use when not using Boolean type?

Question

Ok this might be a brain dead question but I am curious.

I often find I have a situation where I need to store 1 of 3 values in a database. For example if my application asks "Is this house for sale?", the user needs to be able to say "yes", "no" but sometimes the user doesn't know. So there is also "I don't know".

I am always tempted to set my data type as Boolean, but of course that only gives me yes or no and usually this is set to no for default.

I am curious as to what data type is set as common practice in this scenario?

I could use an integer and store "1", "2" or "3". Or I could store a string value, or , or ,or.

This is probably a silly question and there maybe a million ways that this is done. but if anyone knows a best practices method and why that would be helpful. Thanks.

Answer 1

In a database a boolean value can actually expresses three states - true, false, and NULL (provided you allow NULL in the column).

Use NULL for "I don't know".

(And probably set the default for the column to NULL as well)

EDIT: Thinking about this for a moment though, this can be problematic depending on your use case. Some high level languages (Java, for one) would end up converting the NULL to false in the query result set.

::shrug:: Use a varchar(1) ('t','f','u') or the smallest integer value available (for space considerations) ... either is fine.

Using an enum is another option, but be aware that it isn't portable (Oracle being the notable problem child).