What to use when not using Boolean type?

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.

---

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).

---

You could use NULL for "I don't know". You should still be able to have TRUE and FALSE for your boolean type column.

---

Also you can use

ENUM('first','second','third')

it's can be helpful not just with integer numbers, and with string values too

---

I'd advise against using NULL for "I don't know" for the following reasons:

In code, you typically check the value of a bool by asking something like this:

IF myBool THEN...

Or

IF NOT myBool THEN...

...as such, you can't tell the difference between false and 'i don't know'

Additionally, I've found it to be a best-practice for my own situation to always have a default value (false) for bools in my database. One direct effect of nulls in a bool field can be errors in 3rd-party tools (such as MS Access making an ODBC connection to a SQL-Server backend) that can't handle nulls in bool fields gracefully / accurately.

So as for what I do recommend -- I'd say find your smallest number data type (like a tinyint in TSQL) and go with that. Then you can have 0 = false, 1 = true, 2 = IDK.

---

This depends on what data types the server supports. Every data type that is 1 byte can hold up to 255 different values. In MySQL for example, you can use TINYINT. Define numeric constants in your code and use the smalled integer representation possible.