Storing a Yes / No radio button into a MySQL database

Question

I've seen a Yes/No form radio buttons value be stored/saved in a couple different ways. I wonder which way is better and why? This is for a PHP/MySQL application with a typical Yes/No question as part of a form.

1.) Store it as 1, 0 or null. 1 being Yes, 0 being No and null being not answered.

2.) Store it as Yes, No, null. Assume a language conversion can be made.

3.) Use 1, 2 and null so as to better distinct the values.

Thanks, Jeff

Edit: I also must mention that most of the issues have been arising due to jQuery/JavaScript and the comparisons and $() bindings.

Answer 1

Use TINYINT(1). Allow NULL if you wish for the "not answered" option. You can also use BOOLEAN as it's just an alias for the aforementioned datatype. This way of storing boolean data is recommended by MySQL.

More details: Which MySQL data type to use for storing boolean values

Answer 2

Since MySQL has a BOOLEAN type, but it's simply an alias of TINYINT. I recommend against it because the equal sign in PHP == would not distinguish 0 from the lack of value. You'd always need to use triple equal === and it would be easy to make mistakes.

As for your options:

1. This seems the natural choice with PHP, but then you've to be careful to distinguish 0 from the lack of value, so I wouldn't recommend it.

2. I would not recommend this one.

3. Possible, but the assignment to 1 and 2 is somewhat arbitrary and might be difficult to remember and read in code.

What I usually do, is use "Y", "N" and NULL if needed, in a CHAR(1) field, it reads well in code and doesn't create problems.