Best way to represent 3-State Condition?

[DoubleDee]

I have some fields on a form which were originally created as Yes/No, but on further reflection, I think they are better represented as a 3-State Condition.

For example, if the attribute is “Helpful?” and someone made the comment “The President should be impeached!!” then neither “Yes” or “No” really apply since it is a simple statement.

So, here are a few ways I could handle this…

Option #1: Create 3 Values

1 = n/a
2 = No
3 = Yes

Option #2: Create 3 Values (including zero)

0 = n/a
1 = No
2 = Yes

Option #3: Embrace Nulls

NULL = n/a
1 = No
2 = Yes

Option #4: Embrace Nulls and Boolean Convention

NULL = n/a
0 = No
1 = Yes

Option #5: Store Text

n/a = n/a
No = No
Yes = Yes

Personally, I like Option #4 the best.

First of all, if “Helpful?” doesn’t apply, and thus the person doesn’t answer that question, then I think NULL is more accurate than “n/a”. (It is also better from a UI standpoint to not make users choose “n/a” every time something doesn’t apply!!)

Secondly, I try to use 0/1 as much as possible because of the obvious benefits of staying with BOOLEAN values.

Of course, there are LOTS of “Null Haters” out there in the database world that would scoff at me…

What do you think?

Sincerely,

Debbie

[force]

An ENUM data type is available:

http://dev.mysql.com/doc/refman/5.0/en/enum.html

Although I’ve seen numeric values to be a common approach since they’re easier to work with, filter, and match than text.

Null really shouldn’t be used to signify a value–it’s null

[scannon]

Personally, I only use NULL to represent unknown or non-applicable pieces of data.

Another option is for you to create a reference table for all possible states. (Not sure if this would work for your case, but I would probably do something like this.)

CREATE TABLE comment_state (
     ID INT AUTO_INCREMENT PRIMARY KEY
    , Name varchar(10) 
    , Description varchar(100)
);

Then you could input the initial values as such:

INSERT INTO comment_state VALUES 
(DEFAULT, 'Y', 'Yes')
, (DEFAULT, 'N', 'No')
, (DEFAULT, 'NA', 'Not Applicable');

[felgall]

@Force_Flow

An ENUM data type is available:

Unfortunately.

You should avoid using it as it can easily cause problems in the future.

NULL is SQL signifies unknown so you can’t really use it as a value since each NULL is considered to be a separate unknown. With a Boolean field it is a reasonable assumption that around half the NULLs mean true and the other half mean false but with no way of telling which is which or how much it actually varies from that.

[DoubleDee]

@Force_Flow

An ENUM data type is available:

http://dev.mysql.com/doc/refman/5.0/en/enum.html

Although I’ve seen numeric values to be a common approach since they’re easier to work with, filter, and match than text.

Null really shouldn’t be used to signify a value–it’s null

I know some Oracle people who would smack you around for even thinking about ENUM’s…

Debbie

[DoubleDee]

@scannon

Personally, I only use NULL to represent unknown or non-applicable pieces of data.

Then you would like my Option #4…

@scannon

Another option is for you to create a reference table for all possible states. (Not sure if this would work for your case, but I would probably do something like this.)

CREATE TABLE comment_state (
     ID INT AUTO_INCREMENT PRIMARY KEY
    , Name varchar(10) 
    , Description varchar(100)
);

Then you could input the initial values as such:

INSERT INTO comment_state VALUES 
(DEFAULT, 'Y', 'Yes')
, (DEFAULT, 'N', 'No')
, (DEFAULT, 'NA', 'Not Applicable');

I could do that to enforce Referential Integrity, but using Text for the actual values is super messy…

Debbie

[DoubleDee]

@felgall

Unfortunately.

You should avoid using it as it can easily cause problems in the future.

Agreed!!

@felgall

NULL is SQL signifies unknown so you can’t really use it as a value since each NULL is considered to be a separate unknown. With a Boolean field it is a reasonable assumption that around half the NULLs mean true and the other half mean false but with no way of telling which is which or how much it actually varies from that.

Okay, but that is only a partial answer.

Stop sittin on the fence, Felgall!

If you don’t like Nulls, then which option in my OP would be better?

And if you don’t like any of those, then please offer a better solution.

This much I can say…

The more I think about it, I think having “Helpful (Yes/No)?” is a bad idea because…

1.) If often will not apply
2.) People who also think it doesn’t apply will be forced to chose an option
3.) People who also think it doesn’t apply will be forced to chose an option which is misleading!! (Similar to your concern about Nulls.)

Off Topic:

Comment: “The President should be impeached!!”

If you were forced to answer “Helpful? (Y/N)” how would you answer it?

I would say “No”, because it is a statement. But I bet 50% of people would say “Yes” because it is a statement.

My preference is allowing people to skip the question (i.e. NULL), but some like Felgall will disagree…

If I can’t use Null, then that breaks my pretty 0 = FALSE (No), 1 = TRUE (Yes) paradigm.

So, in the end, I feel like if I fix one problem then I create a new one…

Sincerely,

Debbie

[kduv]

You could do Yes/No/I Don’t Know.

Also don’t assume everyone will vote at all. For instance if I didn’t think it was helpful or not-helpful, I just wouldn’t click either.

[DoubleDee]

@kduv

You could do Yes/No/I Don’t Know.

Also don’t assume everyone will vote at all. For instance if I didn’t think it was helpful or not-helpful, I just wouldn’t click either.

You’re assuming a lot.

First, “I Don’t Know” would not make sense in the context of what I explained above.

Second, all questions on the Form are required, so if you decide to take the Survey, then you have to complete it.

Third, if you skipped a question, you would in essence be writing a NULL into the database to which some above has said is an issue.

I agree that people will often choose to skip the entire Survey, but I have decided that if you start a Survey, you need to answer all questions, since half-completed surveys serve little if no value.

And I think forcing people into a Yes/No choice is too rigid, so it’s a question of, “Do I allow NULL or go with something like ‘n/a’, and regardless, how do I represent that on the back-end?” (The last part is the essence of my OP if you missed that…)

Sincerely,

Debbie

[felgall]

@DoubleDee

Stop sittin on the fence, Felgall!

If the NULL is there because they failed to answer the question then that is an appropriate use of NULL.

If they actually answer the question and answer “Don’t Know” then that isn’t an unknown answer as they have actually answered.

Fully normakised you’d have a separate table containing three records for yes, no and don’t know and the main table would use whatever keys you used for that extra table. That’s overkill for a real life solution where likely there will only ever be the three values.

In the real world any of your options 1, 2, or 5 would work provided you included appropriate validation in the code wrapped around the database calls. The way I would be tempted to go though would be a one character field that contains Y for yes, N for no and - for don’t know as that is more readable than your options 1 or 2 in determining what the value represents and is shorter than your option 5.

[DoubleDee]

@felgall

If the NULL is there because they failed to answer the question then that is an appropriate use of NULL.

I would like to change things so a person can a.) Skip the question, b.) Answer “No”, c.) Answer “Yes”.

So NULL would be an appropriate choice.

The next question is…

Will it be a good thing or cause more grief if my code has to deal with {NULL, 0, 1}?

Having 0/1 makes life easy for Boolean, however having to discern between a NULL and 0 could be a pain… :-/

Based on this, I could do one of two things…

Choice #1:

[b]Dropdown	Database
---------	---------[/b]
--		NULL
No		0
Yes		1

Choice #2:

[b]Dropdown	Database
---------	---------[/b]
--		1
No		2
Yes		3

What do you think?

Sincerely,

Debbie

[r937]

oh hai debbie

@DoubleDee

Will it be a good thing or cause more grief if my code has to deal with {NULL, 0, 1}?

in my opinion a very good thing, if you go with NULL meaning not applicable, or question skipped, or no opinion, or didn’t understand… we could go on but stephen will get mad

but in any case i really like NULL with 0 and 1

consider SELECT AVG(answ) AS avg_answ FROM daTable

NULLs are omitted from the calculation…

but the same thing in your 1,2,3 scheme would take more hacking

@DoubleDee

Having 0/1 makes life easy for Boolean, however having to discern between a NULL and 0 could be a pain… :-/

that might not apply in your situation

but if you need to, you can use either COALESCE or NULLIF easily

[DoubleDee]

@r937

oh hai debbie

We’re speaking again, eh?

@r937

in my opinion a very good thing, if you go with NULL meaning not applicable, or question skipped, or no opinion, or didn’t understand… we could go on but stephen will get mad

Wow, I did a “good thing” for once!!

@r937

but in any case i really like NULL with 0 and 1

consider SELECT AVG(answ) AS avg_answ FROM daTable

NULLs are omitted from the calculation…

but the same thing in your 1,2,3 scheme would take more hacking

that might not apply in your situation

but if you need to, you can use either COALESCE or NULLIF easily

Not sure if I would want to do an Average here, but I do agree that it would easier to ignore people who skipped the question as shown below…

[b]Comment #1:[/b]  The President should be impeached!!

Agree: No
Helpful: --


[b]Comment #2:[/b]  Bob's Hardware has solar-powered grills on sale this week for only $199 after rebate!!

Agree: --
Helpful: Yes


[b]Comment #3:[/b]  I find that adding cayenne pepper to my steamed vegetables helps liven them up quite a bit!!

Agree: Yes
Helpful: Yes

For these types of questions, I would more likely want to know…

• Of those who answered, what percentage of people “Agree” with this comment?

• Of those who answered, what percentage of people found this comment “Helpful”?

Sincerely,

Debbie

[felgall]

@r937

in my opinion a very good thing, if you go with NULL meaning not applicable, or question skipped, or no opinion, or didn’t understand… we could go on but stephen will get mad

No I wouldn’t - that’s exactly what NULL is for.

[arout77]

@felgall

No I wouldn’t - that’s exactly what NULL is for.

Yep.

I also don’t like storing integers as representations of booleans ( 0 = no, 1 = yes )

[DoubleDee]

It sounds like we are all in agreement on using…

Dropdown	Database
---------	---------
--		NULL
No		0
Yes		1

More of a PHP question, but since it relates to this topic…

How do ensure that if a user does not select anything in the dropdown menu (i.e. “–”) that I end up with a NULL in the database field and not a Zero-Length String (i.e. ‘’)??

Sincerely,

Debbie

[arout77]

@DoubleDee

It sounds like we are all in agreement on using…

Dropdown	Database
---------	---------
--		NULL
No		0
Yes		1

More of a PHP question, but since it relates to this topic…

How do ensure that if a user does not select anything in the dropdown menu (i.e. “–”) that I end up with a NULL in the database field and not a Zero-Length String (i.e. ‘’)??

Sincerely,

Debbie

If you want to be super-anal and make absolutely certain, you can just check if it is empty; if so, set it as NULL:

<?php 

if( empty( $_POST['helpful'] ) )
    $_POST['helpful'] = NULL;

before inserting it into DB.

But the easiest way to do it is to just set the column up to have the default value as NULL, and then simply omit it from the query.

Two ways to do it:

    insert tbl (other, col1, intcol) values ('abc', 123, NULL)

or just omit it from the column list

    insert tbl (other, col1) values ('abc', 123)

[DoubleDee]

@arout77

If you want to be super-anal and make absolutely certain, you can just check if it is empty; if so, set it as NULL:

<?php 

if( empty( $_POST['helpful'] ) )
    $_POST['helpful'] = NULL;

before inserting it into DB.

But the easiest way to do it is to just set the column up to have the default value as NULL, and then simply omit it from the query.

That seems like a common-sense approach, and one that never occurred to me?!

Well, thanks to everyone for their input and help.

I am glad that I reconsidered how I am doing these types of form questions, and think that I now have a solid way to handle things both on the front-end and the back-end database.

Thanks a bunch everyone!! :tup:

Sincerely,

Debbie

[force]

@DoubleDee

I know some Oracle people who would smack you around for even thinking about ENUM’s…

I said it was available, not that it was the best option

[felgall]

@arout77

<?php 

if( empty( $_POST['helpful'] ) )
    $_POST['helpful'] = NULL;

NULL in PHP has nothing in common with NULL in SQL - they have completely different meanings.

What a PHP NULL would get converted to on being inserted into the database would depend on the field type - as it is a value and not the absence of a value the only SQL value you can be certain it will not be converted to is NULL.

@arout77

or just omit it from the column list

    insert tbl (other, col1) values ('abc', 123)

Not setting a value in the SQL call is the only way to get an SQL NULL in the database.

PHP NULL and SQL NULL have less in common than HAM and HAMSTER