3

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.

Improve this question
0

5 Answers 5

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

SQL Server doesn't have enum either
1

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

Improve this answer

Comments

0

Also you can use

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

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

Improve this answer

3 Comments

yeah((( but author didn't indicate what kind of database he use
So you make an assumption. And one that's not portable. I used to have a guy working for me that did that all the time ...
Actually I never use ENUM. And yes, it's not portable, but there isn't one very good standard (that cover everything in the world of databases), anytime there will be some kind of task that be released differently in different databases
0

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.